DSO COUNTBACK

Hi Wax ,

From your data, the DSO will produce a very aggregated and high-level output, but I expect you to have access to much more granular data than what you’ve shown, such as:

• Posting date
• Customer code (customer name)
• Payment term code
• Transaction amount
• Net due date, etc.

With more granular data, you can calculate a more accurate DSO than the simplified countback method described above by subtracting the posting date from the AR extraction date, multiplying that by the transaction amount, dividing it by the overall AR, and then summing to get the accurate DSO at a specific month-end date.

With this in mind, the solution you're seeking can be achieved from the aggregated data to which you have access in the following manner.

To simplify things for myself while constructing the answer, I have not combined all the steps into one measure using VAR, as it is easier to see the output in each calculated column.

First, create calculated columns to bring the previous month sales, two month prior sales, and three months prior sales, and so on.  

Previous Month Sales = 
VAR PrevMonthDate = EOMONTH('Table'[Date], -1)
RETURN
    LOOKUPVALUE(
        'Table'[Net sales],
        'Table'[Date], PrevMonthDate
    )

Two Months Prior Sales = 
VAR TwoPrevMonthDate = EOMONTH('Table'[Date], -2)
RETURN
    LOOKUPVALUE(
        'Table'[Net sales],
        'Table'[Date], TwoPrevMonthDate
    )

Three Months Prior Sales = 
VAR ThreePrevMonthDate = EOMONTH('Table'[Date], -3)
RETURN
    LOOKUPVALUE(
        'Table'[Net sales],
        'Table'[Date], ThreePrevMonthDate
    )

To check when the net composition of the current month's AR reaches zero or goes negative, you can create a calculated column like the one below:

2 months = [AR]-( [Net sales]+[Previous Month Sales]+[Two Months Prior Sales])

If the negative balance doesn't occur until the 3rd month, (i.e., the current month DSO is longer than 3 months),

3 months = [AR]-( [Net sales]+[Previous Month Sales]+[Two Months Prior Sales]+[Three Months Prior Sales])

You can then produce the countback DSO calculation in the following manner:

DSO = 
VAR CurrentMonthDays = DAY(EOMONTH('Table'[Date], 0))
VAR PreviousMonthDays = DAY(EOMONTH('Table'[Date], -1))
VAR TwoPreviousMonthDays = DAY(EOMONTH('Table'[Date], -2))
VAR ThreePreviousMonthDays = DAY(EOMONTH('Table'[Date], -3))
RETURN
  if([2 months]<0,  CurrentMonthDays + PreviousMonthDays + TwoPreviousMonthDays*(([Two Months Prior Sales]+[2 months])/[Two Months Prior Sales]),
  CurrentMonthDays + PreviousMonthDays + TwoPreviousMonthDays+ ThreePreviousMonthDays*([Three Months Prior Sales]+[3 months])/[Three Months Prior Sales])

The resulting output is as shown below:

An important consideration in the relationship between Accounts Receivable (AR) and sales when calculating Days Sales Outstanding (DSO) is that, in some tax jurisdictions, output VAT is applicable. As a result, the AR balance includes VAT, while sales are recorded net of VAT. In these cases, it's necessary to either gross up the sales figure or net out the AR balance to ensure a proper comparison.

I have attached an example pbix file for your reference.