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