Trended Accounts Receivable Aging – a guide

[DavidKuhry]

The Goal

The business wants to be able to see the total AR by age, trended over time or at any point in time for the recent history.

The Problem

If you have been working with financial data, you likely already have some kind of aging buckets in your Dates Table. However, as we need to be able to evaluate the age at past points in time, these won’t work, because the age is typically tied to that date records distance from “today”. So we will need a novel solution to solve this problem.

My Solution

I say “My Solution” as it may in fact not be the only, or even best solution. This worked and made sense to me, but I welcome and feedback on better or more simplistic solutions.

We will need to create 3 key things:

1. The Aging Buckets
2. A new Dates Table
3. The Measure

The Aging Buckets

Since we cannot use Aging Buckets that may already be in the Dates Table, we will need new ones. For this, I created a new table with 2 columns called “Financial Aging Buckets”.

1. Financial Aging Buckets
   1. The values, 0 – 30, 31 – 60, …, > 1 Year, etc. as text
   2. A sort order column as a whole number (we’ll also use this later in the measure)

The New Dates Table

For this, I created a new Dates Table called “Financial Aging”.

I only brought in 2 columns, the DateKey and the DateValue. I brought the table in from an SQL warehouse, but if you use Power Query to create the Date Table, then you only need the Date Value, as that will be used to join to the existing Dates Table.

Here are the details:

1. Dates Table with only DateKey and DateValue columns
2. Rename DateValue to “0 – 30 Range Upper”
   1.  #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"DateValue", "0 - 30 Range Upper"}}),
   2. Once joined to the existing date table, this field will represent the upper range of 0 – 30 for whatever reference date we are evaluating, i.e. 0 days from today, or the date itself
3. Create 7 more columns, each representing the Upper Range of each bucket, by substracting the appropriate number of days from the “0 – 30 Upper Range” column

#"Added 31-60" = Table.AddColumn(#"Renamed Columns", "31 - 60 Range Upper", each Date.AddDays([#"0 - 30 Range Upper"], -31 )),
#"Added 61-90" = Table.AddColumn(#"Added 31-60", "61 - 90 Range Upper", each Date.AddDays([#"0 - 30 Range Upper"], -61 )),
#"Added 91-120" = Table.AddColumn(#"Added 61-90", "91 - 120 Range Upper", each Date.AddDays([#"0 - 30 Range Upper"], -91 )),
#"Added 121-150" = Table.AddColumn(#"Added 91-120", "121 - 150 Range Upper", each Date.AddDays([#"0 - 30 Range Upper"], -121 )),
#"Added 151-180" = Table.AddColumn(#"Added 121-150", "151 - 180 Range Upper", each Date.AddDays([#"0 - 30 Range Upper"], -151 )),
#"Added 181-365" = Table.AddColumn(#"Added 151-180", "181 - 365 Range Upper", each Date.AddDays([#"0 - 30 Range Upper"], -181 )),
#"Added >1 Y" = Table.AddColumn(#"Added 181-365", "> 1 Year Range Upper", each Date.AddDays([#"0 - 30 Range Upper"], -366 )),

1. I also needed to Transform these new columns to Type Date.
2. I now have a table with all dates, with columns that show Upper Range for each bucket. Again, the Upper Range is the most recent date from each bucket, so for 121-150, it would -121 days from the date evaluated.Load the new Table into the model and Join it to the existing Dates Table.
3. As a note, you could create 7 or 8 new columns in the existing Dates Table. It would be the same. The reason I did not do this is my Dates Table extends 13 months into the future, and for this, I only need these dates up to “Today”. So by doing it this way I save the 13 months’ worth of records and space.

The Measure

The measure will use a SWITCH function with SELECTEDVALUE to determine which Bucket from “Financial Aging Buckets” it is in, and then calculate the amount, filtering each Age Bucket differently using the appropriate combination of Age Range Upper columns from the “Financial Aging” Dates Table.

The SELECTEDVALUE uses the "Order" column from the “Financial Aging Buckets” table so that I do not have to do any string comparisons. I think it is cleaner. 

Each calculation in the SWITCH SUMs the Total AR with a different filter applied. The KEEPFILTERS filters to where the "Invoice Date" (In my code, it is "Date Value Date of Serivce") is (Between) <= MAX Range Upper of the current age bucket, and > MAX Range Upper of the next older age bucket. 

Gross AR Aging = 

VAR _Measure = 
CALCULATE(
    SWITCH( 
        SELECTEDVALUE('Financial Aging Buckets'[Financial Aging Buckets (order)] )
            , 7
                , CALCULATE( [Gross AR (Toggle)]
                    , KEEPFILTERS( 'Financial Transactions'[Date Value Date of Service] <= MAX( 'Financial Aging'[> 1 Year Range Upper] ) )
                )
            , 6
                , CALCULATE( [Gross AR (Toggle)]
                    ,KEEPFILTERS( 'Financial Transactions'[Date Value Date of Service] <= MAX( 'Financial Aging'[181 - 365 Range Upper] ) && 'Financial Transactions'[Date Value Date of Service] > MAX( 'Financial Aging'[> 1 Year Range Upper] ) )
                )
            , 5
                , CALCULATE( [Gross AR (Toggle)]
                    , KEEPFILTERS( 'Financial Transactions'[Date Value Date of Service] <= MAX( 'Financial Aging'[151 - 180 Range Upper] ) && 'Financial Transactions'[Date Value Date of Service] > MAX( 'Financial Aging'[181 - 365 Range Upper] ) )
                )
            , 4
                , CALCULATE( [Gross AR (Toggle)]
                    , KEEPFILTERS( 'Financial Transactions'[Date Value Date of Service] <= MAX( 'Financial Aging'[121 - 150 Range Upper] ) && 'Financial Transactions'[Date Value Date of Service] > MAX( 'Financial Aging'[151 - 180 Range Upper] ) )
                )
            , 3
                , CALCULATE( [Gross AR (Toggle)]
                    , KEEPFILTERS( 'Financial Transactions'[Date Value Date of Service] <= MAX( 'Financial Aging'[91 - 120 Range Upper] ) && 'Financial Transactions'[Date Value Date of Service] > MAX( 'Financial Aging'[121 - 150 Range Upper] ) )
                )
            , 2
                , CALCULATE( [Gross AR (Toggle)]
                    , KEEPFILTERS( 'Financial Transactions'[Date Value Date of Service] <= MAX( 'Financial Aging'[61 - 90 Range Upper] ) && 'Financial Transactions'[Date Value Date of Service] > MAX( 'Financial Aging'[91 - 120 Range Upper] ) )
                )
            , 1
                , CALCULATE( [Gross AR (Toggle)]
                    , KEEPFILTERS( 'Financial Transactions'[Date Value Date of Service] <= MAX( 'Financial Aging'[31 - 60 Range Upper] ) && 'Financial Transactions'[Date Value Date of Service] > MAX( 'Financial Aging'[61 - 90 Range Upper] ) )
                )
            , 0
                , CALCULATE( [Gross AR (Toggle)]
                    , KEEPFILTERS( 'Financial Transactions'[Date Value Date of Service] <= MAX( 'Financial Aging'[0 - 30 Range Upper] ) && 'Financial Transactions'[Date Value Date of Service] > MAX( 'Financial Aging'[31 - 60 Range Upper] ) )
                )
            , CALCULATE( [Gross AR (Toggle)]
                , KEEPFILTERS( 'Financial Transactions'[Date Value Date of Service] <= MAX( 'Financial Aging'[0 - 30 Range Upper] ) ) 
            )
    )
    , KEEPFILTERS( Dates[DateValue] <= MAX( Dates[DateValue] ) )
)

RETURN _Measure

Bringing it all Together

The measure requires the “Financial Aging Buckets” to be present in the visual in order for the SELECTEDVALUE function to work (Or, you could use a single-select slicer). For the Matrix I put it in the rows, and for the Stacked Area chart, the Legend.

I hope someone out there has found this helpful, and Happy Aging!

[Anonymous]

Thank you for all your sharing!