Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Accounts Receivable Aging Report

eHi All   I am a relatively new user in Power BI and am needing assistance with the following requirement. My client is running on Dynamics AX 2012 R2 and would want to replicate The Customer agin...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

 

 

     

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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

       

        

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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.

         

        https://jdsssa-my.sharepoint.com/personal/nehemiahk_just-dynamics_co_za/_layouts/15/guestaccess.aspx?folderid=1ab5ee71949bf4dc996051abdd8af7a59&authkey=AW_YcXqMX_YnuxgvcCdTdd0

         

        Regards

  • bjsp's avatar
    bjsp
    Regular Visitor
    8 years ago

    Am not able to use the Calendar function and then my DueDate and Date columns gives error when I click on quit and apply, and gets out of the query mode. Could you kindly provide your files or provide a solution for it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi bjsp

       

      Can you share the pbix in google drive or one drive and share the link  and the exact problem you are facing to find a solution.

       

      Cheers

       

      CheenuSing