Accounts recieviable ageing - Dynamic date

[Anonymous]

Hi, would be really happy if someone would be able to help me. I am trying to create a report where you are able to see the aging of accounts recievable historically. So if you select a certain date you will be able to see the status on that date. 

I used the solution here https://community.powerbi.com/t5/Desktop/Accounts-Receivable-Aging-Report/td-p/106367 as a help and the end result is supposed to look something like it, just that when I change date it will use the date from the slicer as "today" and then filter based on if it is paid or not on that day.

The invoices and payment files are in different queries so I merged them (Into "Invoice") so that I could get due date and payment date on every row.

I created a table (Calender) to use as a slicer, and then I started creating the measures:

Age = INT ( MAX ( Calendar[Date] ) -  MAX ( Invoice[DueDate]) )

Age 1 - 30 = CALCULATE ( Sum(Invoice[Amount]),  FILTER ( Invoice, [Age] >= 1 && [Age] < 31 ) )

Age  31 - 60 =  CALCULATE (Sum(Invoice[Amount]), FILTER ( Invoice, [Age] > 30 && [Age] < 61 ) )

Age  61-90 = CALCULATE (Sum(Invoice[Amount]), FILTER ( Invoice, [Age] > 60 && [Age] < 91 ) )

Age  over 90 = CALCULATE (Sum(Invoice[Amount]), FILTER ( Invoice [Age] > 90))

Then when i am trying to include the payment date I am not able to make it dynamic, if I filter based on it I only get Invoices that are still not paid today.

I might be in the totally wrong direction, and would greatly appriciate if someone was able to help me

[v-yuta-msft]

@Anonymous,

Could you share more details about your requirement and post some sample data?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I can try, but don't know if I am allowed to post the data all considering

The requirment is two fold

1) Create a table that will show accounts recievable ageing on a spesific date. So that the company can go back and see how much money that where overdue at a certain date (They want to see if there is any effect from changes in invoice routines)

2) Create graphs that show the historical development of the same data, so that they graphically over time can see if there are any effect.

The data is in 2 tables in general:

1) Invoice data, including due date, amount, invoice number, customer name, customer number etc

2) Payment data, including amount, invoice number and payment date

The result will be a table overview like this:

Customer name - 1-30 days - 31-60 days - 61-90 days - 90+ days - Total

           A                  5000                                                       2500          7500

           B                                        4500                                                   4500

           C                  1000                                                                         1000

Total                        6000              4500                                 2500         13000

Then if I change date the data should be as it was that date

Then Graphs based on the totals, going for x months