Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

 
12 REPLIES 12
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 

 

I am not able to download the PBIX. Can you please share an updated link.

Ymir
Regular Visitor

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

I am not able to download the PBIX. Can you please share an updated link.

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.