Your assistance is greatly needed please. I'm looking for the DAX calculation to calculate DSO usisng the Countback Method.
Calculation of DSO in Dec:
Aug Sep Oct Nov Dec
Net Sales 154 159 167 158 205
DSO (Days) 68
Calculation: Dec 30 405-205=200
Nov 30 200-158 = 42
Oct 8 42 - 167 <0; (42/167)*30=8
Secondarily I will need to do this going back for example what was the DSO on Nov 28th? on Oct 31 on Sept 31 etc; to create a graph of DSO by month with a KPI for increase/decrease trendline.
I hope I explained this corrrectly.
You can achieve countback by looping calculation inside sumx formula together with variables.
DSO (CB) = SUMX( // outer loop VALUES('Transaction'[Period]), VAR daysInMonth = 30 VAR ocurrPeriod = 'Transaction'[Period] VAR ocurrARBal = CALCULATE(SUM('Transaction'[A/R]) , FILTER('Transaction', 'Transaction'[Period]=ocurrPeriod)) RETURN SUMX( // inner loop FILTER(ALL('Transaction'[Period]), 'Transaction'[Period]<=ocurrPeriod), VAR icurrPeriod = 'Transaction'[Period] VAR icurrRev = CALCULATE(SUM('Transaction'[Net Sales]) , FILTER(ALL('Transaction'), 'Transaction'[Period]=icurrPeriod)) VAR revSUMX = CALCULATE( SUMX('Transaction', [Net Sales]), FILTER(ALL('Transaction'), 'Transaction'[Period] >= icurrPeriod && 'Transaction'[Period]<=ocurrPeriod) ) VAR revSUMXP = CALCULATE( SUMX('Transaction', [Net Sales]), FILTER(ALL('Transaction'), 'Transaction'[Period] > icurrPeriod && 'Transaction'[Period]<=ocurrPeriod) ) VAR pleft = ocurrARBal - revSUMXP VAR cleft = ocurrARBal - revSUMX VAR multiplier = IF (ocurrARBal > revSUMX, 1, Divide(pleft, icurrRev) ) VAR result = IF (multiplier > 0 || cleft > 0, daysInMonth * multiplier, 0) RETURN result ) )
I am working on DSO caculations and my DSO values should dynamically calculate according to different geograpical aggregations.
Can you please share how you have achived it.
Hi @Polygon ,
I'm a little confused by your description. Sample data and expected output would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly:
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.