DSO DAX Calculation - Countback Method

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:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

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.

Hi,

Were you able to get a solution to this problem?

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
    )
)

Hey just curious if you ever got a solution to this problem. Have been looking to solve the exact problem.

Hi,

I used measure but I can't get it to work for dimensions, for example by geographic area, having related the columns.

Best Regards.

Does anyone got the solution where in we can dynamically filter the DSO calculation on different geograpical leavels.