cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors