DSO COUNTBACK

[Wax]

I've spent days searching forums and using AI, but I haven't been able to get close to the desired result.

I'm trying to calculate a countback DSO using a measure, with the number of days calculated based on how receivables are cleared progressively by invoicing.

I have the total receivables per month, and the total invoicing per month as well.
I want the number of days calculated for each month, as shown in the attached file.

I'm stuck on the month where invoicing exceeds receivables in my several trials.

I've a date table, a sales tables, and an AR table, both related to date table.

Does anyone have any leads?

Thank you very much in advance.

[Pooja_Dasani_98]

Hi @DataNinja777 ,

I have a similar problem, I am trying to calcualte the DSO days using countback method.

I am trying to create a DSO measure that uses the countback method in Power BI. The measure should calcualte the DSO days for the selecetd YearMonth by the user. 

This is the sample data for December 2024 - 

YY-Month	Total sales (Total PPM Revenue)	Total AR	Days
24-Dec	$                     339,388.80	$  1,804,162.75	31
24-Nov	$                     203,538.00	$  1,600,624.75	30
24-Oct	$                     278,860.00	$  1,321,764.75	31
24-Sep	$                     475,688.00	$      846,076.75	30
24-Aug	$                     413,985.00	$      432,091.75	31
24-Jul	$                     325,515.00	$      106,576.75	10
		DSO	163

Here in the July month, the sales is greater than the Total AR so we calcualte 10 as number of days by Total AR/ Total Sales * 30 = 10. The DSO value for Dec 2024 is 163. 

In Power BI, I already have the following measures created. I have two data tables, one for Sales and one for AR. 
1) Total PPM Revenue (From Sales data table)
2) Total AR  (From AR data table)
3) Days in Month (Column coming from Calendar table)
Here is my DAX measure to calcualte the DSO but this gives me the wrong value. Can you please help me troubleshoot htis? 

DSO Countback =

VAR SelectedYearMonth = MAX('Calendar (AsOfDate)'[YearMonthnumber])

VAR ARBalance = CALCULATE([Total AR Amount], 'Calendar (AsOfDate)'[YearMonthnumber] = SelectedYearMonth)

VAR DaysInSelectedMonth = CALCULATE(MAX('Calendar (AsOfDate)'[DaysInMonth]), 'Calendar (AsOfDate)'[YearMonthnumber] = SelectedYearMonth)

VAR DSO_Calculation =

SUMX(

FILTER(

ADDCOLUMNS(

FILTER(

ALL('Calendar (AsOfDate)'),

'Calendar (AsOfDate)'[YearMonthnumber] <= SelectedYearMonth

),

"Revenue", [Total PPM Revenue],

"Days", MAX('Calendar (AsOfDate)'[DaysInMonth])

),

[Revenue] > 0 // Ensure we only consider months with revenue

),

VAR CurrentMonthRevenue = [Revenue]

VAR CurrentMonthDays = [Days]

VAR RevenueToUse = MIN(ARBalance, CurrentMonthRevenue)

VAR DaysAttributed =

IF(

ARBalance <= 0,

0,

IF(

ARBalance >= CurrentMonthRevenue,

CurrentMonthDays,

(ARBalance / CurrentMonthRevenue) * CurrentMonthDays

)

)

VAR RemainingAR = ARBalance - RevenueToUse

VAR ARBalance = RemainingAR

RETURN DaysAttributed

)

RETURN

IFERROR(DSO_Calculation, 0)

[Anonymous]

Thanks for the reply from Greg_Deckler and DataNinja777.

Hi @Wax ,

Have you solved your problem? If it is solved, please mark the helpful replies or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster. Thank you very much for your kind cooperation!

Best Regards,
Zhu

[DataNinja777]

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.

[Greg_Deckler]

@Wax Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.