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
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Anonymous,

What do you mean of "The aging direction( Past Due, Not Due) and the Aging Bucket are being defined in a BI App called ZAP". You’d better load the .pbix file to one drive. And just pot the share link highlighted in yellow backgroud as follows. Thank you for understanding.



Best Regards,
Angelia

  • 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

         

          

    • 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

    • Happyammo's avatar
      Happyammo
      Regular Visitor
      9 years ago

      Hi, I cannot load it. Could I have this pbix?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi

         

        I'm currently developing a similar report for my client too.  I'd be really grateful if you could post an up to date link to the PBIX please?

         

        Kind regards

         

    • bjsp's avatar
      bjsp
      Regular Visitor
      8 years ago

      Can you provide the files both .pbx and excel files. I am getting an error when I am uploading the excel files, after getting out of querry mode, my both date and due date columns are having errors.