Forum Discussion
Accounts Receivable Aging Report
Hi Anonymous
I think I found a solution for you.
I assumed The Date.CalendarDate in the excel provided is the Due Date. Also I noticed there are lot of records with no value under this column. I removed all those rows. I removed all the columns except the CustomerCode, Date and Amount.
1. Loaded this excel data as ARData in the Power BI data model.
2. Created a Calendar table using the command
Calendar = Calendar(Min(ARData[DueDate]),TOday())
3. Added Columns - Year, Month and YearMonth Columns
4. Created a measure under ARData table
WhatistheAge = INT ( MAX ( Calendar[Date] ) - MAX ( ARData[DueDate]) )
The is to create a generic measure that calculates the due date age. The technique here is to take the calendar date
displayed and then subtract the Duedate of ARData. This gives the total what is the age of each transaction.
5. The final step is to create the different Age Measures as follows
6. Age 1 - 30 = CALCULATE ( Sum([Amount]), FILTER ( ARData, [WhatistheAge] >= 1 && [WhatistheAge] < 31 ) )
7.Age 31 - 60 = CALCULATE ( Sum([Amount]), FILTER ( ARData, [WhatistheAge] > 30 && [WhatistheAge] < 61 ) )
8. Age 61-90 = CALCULATE (Sum([Amount]), FILTER ( ARData, [WhatistheAge] > 60 && [WhatistheAge] < 91 ) )
9. Age 91-120 = CALCULATE ( Sum([Amount]), FILTER ( ARData, [WhatistheAge] > 90 && [WhatistheAge] < 121 ) )
10. Age 121 - 150 = CALCULATE (Sum([Amount]), FILTER ( ARData, [WhatistheAge] > 120 && [WhatistheAge] < 151 ) )
11. Age 151+ Days = CALCULATE (Sum([Amount]),FILTER ( ARData, [WhatistheAge] > 150 ) )
12. Current = CALCULATE (Sum([Amount]), FILTER ( ARData, [WhatistheAge] = 0) )
13. NotYetDue = CALCULATE(Sum([Amount]),FILTER ( ARData, [WhatistheAge] < 0) )
14. Create measure Total = sum([Amount])
Now Plot the Table chart with CustomerCode, Total, and measures 6 to 13.
Viola it works.
Sample screen shot
Create a Slicer using Date from using Calendar Table.
If this works for you please accept this as solution and also give KUDOS.
Cheers
CheenuSing
Hi CheenuSing
Sorry for the delay , but i got started working on this today. I am however getting ZEROs against this calculation, this is before I start bucketing the trransactions. Below is the DAX I am using.
AGE = INT ( MAX ( Calendar[Date] ) - MAX ( CustTransOpen[TransDate]) ).
Am sure I am getting something terribley wrong or slightly , I am using TRansDate instead of the Due Date.
Regards
Hi Anonymous
It may be zero or any value depending on what is the max(Calendar[Date]) and MAX ( CustTransOpen[TransDate]).
Create a measure and individually check out.
This will be so because there is no filter context applied. Once you create a report with the various dimensions and age bucket values it will calculate according to each row.
Hope this helps.
Cheers
CheenuSing
Hi CheenuSing
Thank you again for your valuable responses. I have attached a link with the model I have created and a sample set of data. Kindly consider the TRANSDATE , ACCOUNTNU, and AMOUNTCUR for testing. The Model has all the calucations but in my report I only see the The Total and Current Colums yet the dates are different per transaction.
In your earlier explanation, I did not pick where I can be able to filter my report to a prior period, with the open transactions of that period being aggregated in the Current Column?
The organisation has defined accounting periods and probably the best explanation i should have given is that they want to run their aging by account period i.e. SUM up open transactions in a particular period and represent them in one column. Depending on the Date Slicer, The date in context becomes teh Current and prior periods are represented according right up to where i have bucket 121-150 Days and thereafter sum up all the oldest ones into the 151 + Days column. My modle should have the FiscalCalendar Periods table with STARTDATE and ENDDATE.
Below is the link to sample date and microsoft Powerbi Desktop document.
Regards
Hi Anonymous
My first observation is you should remove the relationship between the Calendar Table and CUSTTRANSOPEN (TRANSDATE).
You should be able to see the distribution by age.
As for the slicers what are slicers you are planning. If it is only Calendar Date it should be Ok. This is true irrespective of Calendar or Fiscal Year.
Check it out and let me know.
Cheers
CheenuSing
Hi CheenuSing
Thank you so much for your assistance, I removed the relatioship between the Date Table and the Customer Transaction Table and it is aging out correctly. A good start.
So now , my other issues is how do i change this report to create and populate buckets based on the accounting periods or based on a fiscal or calendaer month that starts 01/01/2016 to 31/01/2016 etc. Its basically an aging report based on Accounting Periods for an irganisation.
Regards
Hi Anonymous
Glad to note it worked for you. From my understanding of Ageing Report it should be taken as at the end of a period, say Date, Monthend or YearEnd.
Just change the total measure as
Total = calculate (SUm(ARData[AMOUNTCUR]), FILTER (CUSTTRANSOPEN, [TRANSDATE] <= max('Calendar'[Date]) ))
What this will do is consider only transactions between the first transaction till the period end date.
Say you have selected YearMonth = 201610. The above expression will consider from the first date of transaction till 31/10/2016.
In such cases we should remove showing NotYetDue from your report.
I hope you have already created the FiscalYear and FiscalYearMonth Columns in your calendar table.
Try it out.
Cheers
CheenuSing
Hi CheenuSing
I will try this out and let you know.
Based on comment 'Say you have selected YearMonth = 201610. The above expression will consider from the first date of transaction till 31/10/2016' if I select multiple YearMOnt 201610 201609 201608 201607 201606 will this dynamically categorise, not the startdate and enddate of each of the periods?
Regards
Hi Anonymous
I am not clear why you should select Multiple Year Month in a Ageing report. It is always as at the end of the period.
Assuming even you select multiple year months, it will take end date of the max of year month.
if you select multiple YearMOnt 201610 201609 201608 201607 201606 , it will show as of 31/10/2016.
The starting point will not be 01/06/2016 it will be from the First Transaction Date of your CUSTTRANSOPEN.
Hope I have made this clear.
If my understanding is nor right please share what is the scenario of period selection and what kind of output you expect.
What transaction dates it should consider - from date and to date ?
Cheers
CheenuSing
g