Tuesday, 27 November 2018

Aging Accounts Receivable in DAX

When your collection teams are working old accounts receivable and catching with current billing, things can quickly get overwhelming.
Sure the accounts balance is an important priority criterion but you also need also track other features and prioritise your efforts based on that. Probably, the simplest feature you can build is the accounts aging. 
To do so, the simple way is by using your favorite Self BI tool, I have a preference for Microsoft Power BI, and build you aging reports by classifying accounts 
that are more than 60, 90, 120, and 180 days old, which will help you focus on the ones that you better chance to collect first . Naturally, the older the A/R is ( 180-day+ ), the less likely you might get paid on it. 

Then, you can decide, based your write off policy (which will impact your expected credit losses, IFR9) which Accounts should be written off, considered bad debt, and probably outsourced to Debt Collection Agencies.
We will show in the following how you can define your buckets in DAX (Power BI Language) :

Suppose Receivables[] is the table containing details about Receivables accounts such as Due Date, Due Amount, Customer etc..

For instance, let's build the bucket for the 1 to 60 Days Bucket as a new measure :

-------------------------------------------------------------------
1 to 60 Days =

    IF( ISBLANK(         CALCULATE(
           [Due Amount],  FILTER(VALUES('Receivables'[Due Date]), INT( TODAY() - 'Receivable Item'[Due               Date] ) <= 30 )))
        ,0
        CALCULATE(
           [Due Amount],  FILTER(VALUES('Receivables'[Due Date]), INT( TODAY() - 'Receivable Item'[Due               Date] ) <= 30 )
    ))
-------------------------------------------------------------------
This formula can then be rewritten for the other buckets :  61 to 90 Days, 91 to 120 Days121 to 180 Days and Over 180 Days

No comments:

Post a Comment