DSO DAX Calculation - Countback Method

[Polygon]

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

A/R                                                                 405

DSO (Days)                                                     68

                                            Days            Calculation

Calculation:            Dec      30                405-205=200

                               Nov     30                 200-158 = 42

                                Oct       8                  42 - 167 <0; (42/167)*30=8

                                                                         =============

                                                                                 68

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.

Thanks!

Paula

[RAVISHhcl]

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

[MaxGab1968]

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.

[Ymir]

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

[bcteh]

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

[Ymir]

Hey this is interesting.

Would you be so very kind to attach the pbix file with the above solution (if you have it).

Thank you so much

[bcteh]

Here you go..... the snippet of the file.

PBIX file is shared in one drive below.
DSO Countback.pbix 

[Ymir]

Thank you so much 🙂 This saved me a lot of work.

[RAVISHhcl]

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.

[Ashish_Mathur]

Hi,

Were you able to get a solution to this problem?

[v-joesh-msft]

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.