Forum Discussion
Polygon
6 years agoHelper I
DSO DAX Calculation - Countback Method
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 Oc...
bcteh
6 years agoFrequent Visitor
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
)
)