Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Aging Bucket for AP and AR

Hello,

I want to create an Account Receivable and Account Payable report in Power BI. I have a table with the due date, payment date, and invoice date. The buckets will be
>=0- If the due date-cut off date(end date of every month) is less than or equal to 0
1-15-If the due date-cut off date(end date of every month) is between 1 to 15
16-30-If the due date-cut off date(end date of every month) is between 16 to 30
31-60-If the due date-cut off date(end date of every month) is between 31 to 60

Example let say cut off date is 31st March 2019, so it will take in all the records whose payment date is greater than the cut off date and invoice date is less than cut off date. Then it will group those records in the age buckets as depicted above.

It should to it for every month like that and then group the records amount the aging bucket according. I want a chart that will have 13 months(current to last 13 months) with the buckets ranges for each month. For each month the cut off date is set to be the last date of that particular month and then the buckets are calculated accordingly dynamically.

Please, can anyone help me with such Dynamic Aging for each month?

Thanks

Shubs

Interesting Links
Need Help
Tutorial Requests

3 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Shubs, 

    I am not clear  about your requirement, so if possible, could you please inform me your detailed information ( such as your data sample and your expected output)? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello dax ,

      Thanks for your response. Please see the screen below

      So if you see, I want a 12-13 month view like this of the specific buckets as explained earlier. For example, let say for month of Oct, it should include all the records in which payment date is greater than 31st Oct and Invoice date less than 31st Oct. Then the aging bucket is calculated on the basis of Due date- Cut Off Date(31st Oct in this case).

      Now if we get to Sep then the Cut off date will be 30th Sept and all the above explanation is the same just that 31st Oct is replaced with 30th Sept.

      Hope you understand the problem. I have one table that has all the date values(due date, payment date, and Invoice date) and the amount value as well. Can you please help me with the Dax calculation for this?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi dax ,

        Any update on it?

        I have created a DAX calculated column which solves a bit of problem but fails when the conditions are satisfying for the two.
        For Example, let say one of my records in the data is as follow
        Invoice Date- 12/12/2018
        Payment Date- 2/1/2019
        Due Date-1/31/2019
        Let say now my cut off date is 1/31/2019. It satisfies the condition that invoice date<cut off and payment date >cut off date.
        But if my cut off date is 12/31/2018 then also it satisfies the condition.

        You can say it as duplicate but basically I want to treat every month separately and include the records based on the cut off date. So, my table has 1 record, but I want when I add the calculated column then my table gets a duplicate/same record as well. The only difference will be that one record for cut off date 1/31/2019 and one record for 12/31/2018.
        It has to be dynamic for the last 13 months as you will see my calculated column condition below.

        Are you clear about my requirements?

        Here is the Calculated Column formula

        Test =
        Var CurrentCutoff= EOMONTH(TODAY(),0)
        Var PrevCutoff= EOMONTH(TODAY(),-1)
        Var Prev2Cutoff= EOMONTH(TODAY(),-2)
        Var Prev3Cutoff= EOMONTH(TODAY(),-3)
        Var Prev4Cutoff= EOMONTH(TODAY(),-4)
        Var Prev5Cutoff= EOMONTH(TODAY(),-5)
        Var Prev6Cutoff= EOMONTH(TODAY(),-6)
        Var Prev7Cutoff= EOMONTH(TODAY(),-7)
        Var Prev8Cutoff= EOMONTH(TODAY(),-8)
        Var Prev9Cutoff= EOMONTH(TODAY(),-9)
        Var Prev10Cutoff= EOMONTH(TODAY(),-10)
        Var Prev11Cutoff= EOMONTH(TODAY(),-11)
        Var Prev12Cutoff= EOMONTH(TODAY(),-12)
        Var DaysDues= 
        IF( Table[invdate]<=CurrentCutoff && Table[paydate]>=CurrentCutoff,Table[duedate]-CurrentCutoff,
        IF( Table[invdate]<=PrevCutoff && Table[paydate]>=PrevCutoff,Table[duedate]-PrevCutoff,
        IF( Table[invdate]<=Prev2Cutoff && Table[paydate]>=Prev2Cutoff,Table[duedate]-Prev2Cutoff,
        IF( Table[invdate]<=Prev3Cutoff && Table[paydate]>=Prev3Cutoff,Table[duedate]-Prev3Cutoff,
        IF( Table[invdate]<=Prev4Cutoff && Table[paydate]>=Prev4Cutoff,Table[duedate]-Prev4Cutoff,
        IF( Table[invdate]<=Prev5Cutoff && Table[paydate]>=Prev5Cutoff,Table[duedate]-Prev5Cutoff,
        IF( Table[invdate]<=Prev6Cutoff && Table[paydate]>=Prev6Cutoff,Table[duedate]-Prev6Cutoff,
        IF( Table[invdate]<=Prev7Cutoff && Table[paydate]>=Prev7Cutoff,Table[duedate]-Prev7Cutoff,
        IF( Table[invdate]<=Prev8Cutoff && Table[paydate]>=Prev8Cutoff,Table[duedate]-Prev8Cutoff,
        IF( Table[invdate]<=Prev9Cutoff && Table[paydate]>=Prev9Cutoff,Table[duedate]-Prev9Cutoff,
        IF( Table[invdate]<=Prev10Cutoff && Table[paydate]>=Prev10Cutoff,Table[duedate]-Prev10Cutoff,
        IF( Table[invdate]<=Prev11Cutoff && Table[paydate]>=Prev11Cutoff,Table[duedate]-Prev11Cutoff,
        IF( Table[invdate]<=Prev12Cutoff && Table[paydate]>=Prev12Cutoff,Table[duedate]-Prev12Cutoff,
        BLANK())))))))))))))
        Return
        if(DaysDues<=0,"C",if(DaysDues<=15,"1 to 15",if(DaysDues<=30,"16 to 30",if(DaysDues<=60,"31 to 60",">60"))))