AR Aging Buckets (Dynamic Based on User Selected CutOff Date)

[tachydidaxy]

I'm trying to put together an AR Aging report that will allow a user to select a cut-off EOM date and which will then dynamically categorize all receivables in aging buckets.

I've been able to get a start on it, but I'm having issues and was hoping for some insight from the community.

I've attached a sample anonymized PBIX file called aging_ex.pbix. This file has only one AR account, but it should be enough data.

As you can see from the above image - it's a simple model with a AR_Transactions, AR_Account, and Date tables being the only ones linked with relationships. AR_Account[account_id] is linked to AR_Transactions[ar_account_id] and AR_Transactions[transaction_date] is linked to Date[Date].

I then used two disconnected tables, Aging_Bands for the buckets we've defined, and EOM_Date, as a selector for the cut-off/EOM date to run the report based against.

My AR_Transactions table has only the following columns:

I've been able to get the results I want, but only with charges to the AR account. I'm struggling with how to add in the payments.

My formula for the charges is as follows and works as expected:

AR Aging Value = 
VAR _test =
    CALCULATE (
        [AR Charges Total],
        FILTER (
            AR_Transactions,
            AR_Transactions[due_date] <= [Selected Month End Date]
                && COUNTROWS (
                    FILTER (
                        Aging_Bands,
                        [Age of Debt] >= 'Aging_Bands'[From]
                            && [Age of Debt] <= 'Aging_Bands'[To]
                    )
                )
                    > 0
        )
    )
RETURN
    IF ( ISBLANK ( _test ), 0, _test )

As you can see from the screen capture below, choosing different EOM dates from the slicer adjusts the values of the charges correctly and puts them in the correct aging buckets based on the number of days elapsed from the AR_Transactions[due_date] and the [Selected Month End Date].

My dilemma, however, is how to add in the payments!

Since payments are simply listed as a payment type in the AR_Transactions table, I'm not sure how I'd apply the payments to the overall totals, but making sure to apply to the oldest charges first and then moving any remaning amount to the next oldest charges.

I feel there must be something that I'm missing. 

Any advice is greatly appreciated.

[v-yulgu-msft]

Hi @tachydidaxy,

---

 Since payments are simply listed as a payment type in the AR_Transactions table, I'm not sure how I'd apply the payments to the overall totals, but making sure to apply to the oldest charges first and then moving any remaning amount to the next oldest charges.

---

What is your desired result for payments? Would you please illustrate how to calcualte payments with some examples?

Regards,

Yuliana Gu

[tachydidaxy]

Thanks @v-yulgu-msft 

Let's see if I can better communicate this:

1. User selects the desired EOM date from the slicer - this is the cut-off date from which the report is being run.
2. Desired goal is to show the AR Aging balance from the account's creation date up to the selected EOM date
3. All charges applied to the AR Account with a transaction_date on or prior to the selected EOM should be summed
4. We then age the charges based how many days have passed from the transaction's due_date to the selected EOM date
5. Similarly, all payments applied to the AR Account with a transaction_date prior to or on the selected EOM date should be summed
6. We then need to apply the summed payments made in the selection scope to the aged charges, only we need to apply payments to the oldest charges first (i.e. start at 91+ bucket) and if there is any remainder, it will then be applied to the next oldest charges that are available (i.e. 61-90 bucket, then 31-60 bucket and so on).

From what I can tell looking at other posts for aged debtors, their models normally have a "cleared_date" of some sort associated with the transaction charges. So they have trans_date, or when the charge actually took place, and then a clear_date, or when the charge was indicated as being paid. If I had a cleared_date in my data that would make this exercise trivial, unfortunately all I have are transaction dates - there is no separate field that contains the date when the charge has officially been considered paid. It all has to be calculated on the fly.

I've linked to a newer version of the PBIX which has some small changes to hopefully make my needs clearer. It includes some cards to show all the charges that were made prior to the cut off date as well as all the payments made prior to the cut off date. I'm using 5/31/2013 for my example here.

As you can see the $1,964.29 for the charges card in the picture above is simply all the charges shown in the table below added ($337.26 due 3/31/2013 + $928.96 due 4/30/2013 + $356.84 due 5/31/2013 = $1,623.06 + $341.23 due 6/30/2013 = $1,964.29)

Additionally, the payments shown in green above add correctly up to show that this account paid their balances due by 5/31/2013 in full. (Remember the $341.23 charged to the account between 5/17/2013 adn 5/31/2013 are not due until the following month 6/30/2013).

And here is a really bad mock-up of what I was hoping I could achieve. I'd basically want another row underneath the charges row for the account id that shows the payments being applied. In this case, since this account has paid all charges on time they all zero out - but not all accounts will be like this; there will be times where underpayments and/or over-payments are made. I've broken it down in to three additional rows only to show how were taking the total available payments for the period (in red text) and applying them to the buckets in reverse order of age (oldest first).

One last mock up of my desired end visual - this is just typed up in Excel not based on any real data, but should give a good idea of what I'm aiming for:

[tachydidaxy]

So after thinking about this some more, I realized the only way to do this was to actually do it. 😕

I was able to cobble this together which works so far for the accounts I've tested it on. I've been limiting the account_ids to a few at a time, but will start testing it on the entire table. I'm interested to see what the performance will be like. 

I'm sure there's a better way to approach and would love any suggestions from the field.

GBF = 
VAR _Credits = [Payments in Selected Scope]
VAR _Debits = [Charges in Selected Scope]
VAR _SelectedEOM = [Selected Month End Date]
VAR _Current = [Current Charges]
VAR _30 = CALCULATE ( [Aging Value], FILTER ( ALL ( Aging_Bands ), Aging_Bands[band_name] = "0 - 30" ) )
VAR _60 = CALCULATE ( [Aging Value], FILTER ( ALL ( Aging_Bands ), Aging_Bands[band_name] = "31 - 60" ) )
VAR _90 = CALCULATE ( [Aging Value], FILTER ( ALL ( Aging_Bands ), Aging_Bands[band_name] = "61 - 90" ) )
VAR _91 = CALCULATE ( [Aging Value], FILTER ( ALL ( Aging_Bands ), Aging_Bands[band_name] = "91+" ) )
VAR _Remainder91 = IF ( _91 <> 0, _Credits + _91, _Credits )
VAR _Result91 = IF ( _Remainder91 <= 0, 0, _Remainder91 )
VAR _Remainder90 = IF ( _90 <> 0, _Remainder91 + _90, _Remainder91 )
VAR _Result90 = IF ( _Remainder90 <= 0, 0, _Remainder90 )
VAR _Remainder60 = IF ( _60 <> 0, _Remainder90 + _60, _Remainder90 )
VAR _Result60 = IF ( _Remainder60 <= 0, 0, _Remainder60 )
VAR _Remainder30 = IF ( _30 <> 0, _Remainder60 + _30, _Remainder60 )
VAR _Result30 = IF ( _Remainder30 <= 0, 0, _Remainder30 )
VAR _RemainderCurrent =
    IF (
        _Current <> 0
            && _Result30 > 0,
        _Current,
        IF (
            _Current <> 0
                && _Remainder30 <= 0,
            _Remainder30 + _Current,
            IF ( _Current = 0 && _Remainder30 < 0, _Remainder30, 0 )
        )
    )
VAR _ResultCurrent = IF ( _RemainderCurrent <= 0, 0, _RemainderCurrent )
VAR _Total = _Result91 + _Result90 + _Result60 + _Result30 + _RemainderCurrent
RETURN
    IF (
        HASONEVALUE ( Aging_Bands[band_name] ),
        SWITCH (
            VALUES ( Aging_Bands[band_name] ),
            "91+", IF ( ISBLANK ( _Result91 ), 0, _Result91 ),
            "61 - 90", IF ( ISBLANK ( _Result90 ), 0, _Result90 ),
            "31 - 60", IF ( ISBLANK ( _Result60 ), 0, _Result60 ),
            "0 - 30", IF ( ISBLANK ( _Result30 ), 0, _Result30 ),
            "Current", _RemainderCurrent,
            0
        ),
        _Total
    )