cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Polygon
Helper I
Helper 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     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

 
10 REPLIES 10
RAVISHhcl
New Member

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

MaxGab1968
New Member

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
Regular Visitor

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

bcteh
Frequent 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
    )
)
Ymir
Regular Visitor

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
Frequent Visitor

bcteh_0-1629859667720.png

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

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

 

Ymir
Regular Visitor

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

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
Super User
Super User

Hi,

Were you able to get a solution to this problem?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-joesh-msft
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors