Accounts recieviable ageing - Dynamic date

Question

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 · Answer

Anonymous,
&nbsp;
Could you share more details about your requirement and post some sample data?
&nbsp;
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 · Answer

I can try, but don't know if I am allowed to post the data all considering&nbsp;The requirment is two fold&nbsp;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.&nbsp;The data is in 2 tables in general:&nbsp;1) Invoice data, including due date, amount, invoice number, customer name, customer number etc2) Payment data, including amount, invoice number and payment date&nbsp;The result will be a table overview like this:&nbsp;Customer name - 1-30 days - 31-60 days - 61-90 days - 90+ days - Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1000Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;13000&nbsp;Then if I change date the data should be as it was that date&nbsp;Then Graphs based on the totals, going for x months&nbsp;

Forum Discussion

Accounts recieviable ageing - Dynamic date

2 Replies

Related content

Community account support

Dynamic Accounts Receivable Aging

Dynamic Filter

Dynamic currency conversion based on Account Manager location in Power BI

Power BI Account Blocked