Forum Discussion

Ericshepdawg's avatar
Ericshepdawg
Frequent Visitor
3 years ago

AR aging trend graph

Hi all, I have a measure that successfully calculates which invoices are 31-60 days overdue as of today (see below). However, I want to create a rolling trend graph that shows the sum of invoices aged 31-60 days from the selected period in a slicer, and then the sum of invoices aged 31-60 days from each month going back 12 months from that selected period. For example, if I selected August, 2023, it would calculate the sum of invoices that were aged 31-60 days as of the last day in August, then the same thing but as of the last day in July, then last day of June etc. This would sit on a line graph to show the trend of invoices aged 31-60 days during different months. I have a calulcated column called [lastdayinperiod] which is to be referenced within this measure. However, I am struggling to create the right strucutre for this measure. Any help is much appreciated!

Sum of overdue invoices aged 31-60 days as of today:

_M_AR_31-60 =
VAR SelectedPeriod = CALCULATE(MAX('_MasterDates_Disconnected'[Period]), TODAY() <= _MasterDates_Disconnected[LastDayInPeriod])
VAR AR_3160 =
CALCULATE(SUMX(_Fact_AR, _Fact_AR[TotalBilled]), _Fact_AR[InvoiceDueDate] >= TODAY() - 60 && _Fact_AR[InvoiceDueDate] <= TODAY() - 31 && _Fact_AR[Period] <= SelectedPeriod)
- //Minus any receipts entered with Invoice
CALCULATE(SUM(_Fact_AR[Received]), ISBLANK(_Fact_AR[CreditInvoiceDate]) && _Fact_AR[InvoiceDueDate] >= TODAY() - 60 &&_Fact_AR[InvoiceDueDate] <= TODAY() - 31 && _Fact_AR[Unassigned] <> "Unassigned")
-
CALCULATE(SUM(_Fact_AR[Received]), NOT(ISBLANK(_Fact_AR[CreditInvoiceDate])) && _Fact_AR[InvoiceDueDate] >= TODAY() - 60 && _Fact_AR[InvoiceDueDate] <= TODAY() - 31 && _Fact_AR[Unassigned] <> "Unassigned")
RETURN
IF(AR_3160 = 0, BLANK(), AR_3160)
Need Help

2 Replies

    • Ericshepdawg's avatar
      Ericshepdawg
      Frequent Visitor
      3 years ago

      Hi Amit, thanks for the response. One question, wouldn't this only be a snapshot of the current time? I am looking to build a rolling trend over 12 months that's dynamic based on a slicer. I want to analyze each of the 12 months that occured before the date I select on my slicer, and plot each of those 12 months on a line graph. Wondering if the buckets could still be useful, but I would somehow need to turn them into a time based trend. The part I am struggling with the most is getting the measure to change the period end it's referring to in order to calculate AR aging. For example, I have no problem getting it to reference the current date, but I need it to calculate the aging from 12 different period end dates, in order to see the AR aging for all 12 months in the past in one graph. I hope this makes sense and thanks so much for your help!