Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Getting Current, 30, 60, 90 Day AR Aging

I have an Accounts Receivable table called "AR" that has a Date column and a Transaction Amount column. 

I have your standard "Date" table. The two tables are related via the Date Columns.

I want to create a measure(s) to calculate the Current (Today), 30 (1-30), 60 (31-60) and 90+(61+) days aging amounts.

 

My current code just to get 30 days is as follows: 

30Days = 
Calculate(
    'Sum('AR'[Transaction Amount]),
    DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-30,Day)
)

I am getting an amount, but nothing even close to the true amount.

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    It works fine on my pbix I tested.

    Sample data

    Table AR:

    Relationship:

     

    The slicer is created by the date field from Date table.

     

    You can check more details from the attachment.

     

     

    Best Regards,

    Stephen Tao

     

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

     

     

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago
    30Days = 
Calculate(
    'Sum('AR'[Transaction Amount]),
    DATESINPERIOD('Date'[Date],Today()-1,-30,Day)
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      The issue I have with this is the "Today()-1" bit. 

      I am not looking to base this off of Todays date. I want to base it off my selected date on the slider. Lets say I have 1/1/2021 - 11/15/2021 selected I want to see the Current, 30, 60, 90+ based on the 11/15/2021 date

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    It works fine on my pbix I tested.

    Sample data

    Table AR:

    Relationship:

     

    The slicer is created by the date field from Date table.

     

    You can check more details from the attachment.

     

     

    Best Regards,

    Stephen Tao

     

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