Forum Discussion

Wax's avatar
Wax
Regular Visitor
1 year ago

DSO COUNTBACK

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...
Pooja_Dasani_98's avatar
Pooja_Dasani_98
Frequent Visitor
1 year ago

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)