Aging report in Power BI - YouTube

- Today, we are gonna build an aging report in Power BI.

So first of all, what is an aging report?

An aging report is a way to see when invoices are due

from different customers.

You can see, this is an example,

we have five different customers,

they have different invoices

that are due at different times,

and we can see what time the different invoices

or the sum of the different invoices are due.

The task we'll go through is we'll first describe the task,

of course, that is create an aging report in Power BI,

connect to data source and move data to the data warehouse;

that is already done.

It would be too long of a video

if I should also do that now,

build a dataset view in the data warehouse,

then we will build a dataset in Power BI

and then we'll build the Power BI report.

So to the view.

This is a standard data...

Oh, this is a Snowflake data warehouse.

As you can see, i have already written the view.

It is a little more than a than 60 rows.

The most important thing is that as you can see here,

days till payment,

if it is less than zero, then we know it's overdue.

If it's less than 30, then we know

that it is between zero and 30 days and so forth.

We will use that later.

So we will create the view.

The view is called aging.

And now we got a view.

So connect to the view.

Now we are connecting to the data warehouse.

We will find the view that we just created.

So you can either use import or direct query.

Import means that you are moving the data,

all the data from Snowflake into Power BI

and direct query means that every time you feel so

or you do anything with the data,

you will go and ask Snowflake to renew your data.

Usually, as a rule of thumb, if you have a large amount

of data like a really large amount of data,

you will choose direct query,

and also, if you need to do some complex thing

that only Power BI can do, then you have to choose import.

In this case, we have very little amount of data.

So we will just use import.

And now to create the dataset.

First of all, we need to set everything

to the right data types.

So as you can see, this year has chosen a decimal number,

but it should be a whole number.

So transform data type, whole number.

Days till payment, that is also a whole number.

This is also a whole number.

And that's it.

Close and apply.

And now Power BI is pulling all the data from Snowflake.

So now it's time to create the dataset,

the Power BI dataset.

So there's a lot of cleaning up to do.

What we're doing here is that we are making everything

a lot easier to work with when we are building the report.

We are removing a lot of the rows that are not necessary,

and we are formatting and we are changing data types

when we need to.

So first this one,

this is invoice ID, don't summarize.

And height.

Invoice date, it is a data type, so this is correct,

but we want a better format.

We will choose the ISO format, year, month, date.

We will also do this here.

There we go.

Days till payment, it doesn't make sense to sum them,

maybe we could say this should be an average,

but in this case, we will just not summarize.

Sort.

This is definitely not a summarize,

but we will hide this one

and I'll come back to why I added this one.

This one here, again, an ID, don't summarize.

Again.

Don't summarize.

And in this case, we won't need this one in this report.

Total invoice.

This is a currency.

It is Euro.

And to make everything a bit more pretty,

we will put all the dates in a folder.

And that's it.

Save everything.

So now we got a beautiful dataset,

and now we can build the report.

We will just create a simple matrix, aging description.

We will put this one in the column,

customers at the row.

And you can see I made a mistake.

So now we have invoice in total.

To make everything a bit more viewable, we can...

Actual size.

And as you can see, everything is in the wrong order

and we would like it to be in a better order.

So that is why I created the order by.

So we will go in,

choose this one, column two, sort by column, aging sort.

And that's it.