cancel
Showing results for
Did you mean:
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
New Member

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

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.

Regular Visitor

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

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

Frequent Visitor

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

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

Regular Visitor

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

New Member

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.

Super User

Hi,

Were you able to get a solution to this problem?

Regards,
Ashish Mathur
http://www.ashishmathur.com
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:

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.

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

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