DSO Calculation - referencing calculated table

DAX DAX Calculations
1

Hi guys
I am struggling with calculation of Daily Sales outstanding using the countback method.
This takes current amount outstanding and reduces the amount by sales until the amount is 0. then the difference in days between selected date and the day the balance becomes zero is the DSO.

Initially I am working with a static dax table to get the logic right, but will change the account and dates to MAX once comfortable with logic.
Plan is to generate a table using ADDCOLUMNS
col of last 365 days. (Done)
col for Current balance into all rows (Done)
col to index days in reverse order (Cannot get to work)
col to pick up daily sales balances (Cannot get to work)
Col to run cum total working balance less daily sales with EARLIER on index days
Then Count rows . greater than 0

My Dax code is
ZZ DSO4 =
VAR myDate = CALCULATE(max(‘Calendar’[Date]),‘Calendar’[Date]= Date(2020,3,31))
var myDateRange = DATESINPERIOD(‘Calendar’[Date],mydate,-31,DAY)
Var myAccount = “C0057”
Var myAccountBalance = CALCULATE([AccountBal],FullFile[AccountCode]=“C0057”,FullFile[DebtorDate]<=myDate)
Var mSales = CALCULATE([SumBalance],FILTER(FullFile,FullFile[CDKFile]=“Trans”),FILTER(FullFile,FullFile[DocumentType]<>“P”),FullFile[AccountCode]=myAccount)
var msales2 = CALCULATE(sumx(FullFile,FullFile[Balance]),FILTER(FullFile,FullFile[CDKFile]=“Trans”),FILTER(FullFile,FullFile[DocumentType]<>“P”),FullFile[AccountCode]=myAccount)
Var myTab = ADDCOLUMNS(myDateRange,“mDate”, ‘Calendar’[Date],“Bal”,myAccountBalance)

Var myTab2 = ADDCOLUMNS(myTab,“Index”,RANKX(myTab,‘Calendar’[Date],1,DESC),“DailyTot”,mSales)
Return
myTab2

The index and daily sales are summing the whole table and not placing the daily sales in the correct row for the day. Looks Like a TREATAS, but I can’t seem to reference the columns gernerated in AddCOLUMNS variable. Also get messages like “col not in base table”

SO firstly - Is this the right approach to calculate DSO - feels right and better than my first attempt.
Secondly - how do I reference the virtual tables to summarise amounts by day?

PBIX also attached - DAX table is ZZ DS04

Thanks DebtorsEDNA.pbix (448.2 KB)
Pete

2

Hi All
I’ve managed to move this one on a little with following:

VAR myDate = CALCULATE(max(‘Calendar’[Date]),‘Calendar’[Date]= Date(2020,3,31))
var myDateRange = DATESINPERIOD(‘Calendar’[Date],mydate,-31,DAY)
Var myAccount = “C0057”
Var myAccountBalance = CALCULATE([AccountBal],FullFile[AccountCode]=“C0057”,FullFile[DebtorDate]<=myDate)

Var Preptest = SUMMARIZECOLUMNS(‘Calendar’[Date],
myDateRange,
“Bal”, myAccountBalance,
“Sales”, CALCULATE(sum(FullFile[Balance]),FILTER(FullFile,FullFile[CDKFile]=“Trans”),FILTER(FullFile,FullFile[DocumentType]<>“P”),FullFile[AccountCode]=myAccount),
“index”, DIVIDE(mydate,CALCULATE(sum(‘Calendar’[Date])),0)*1000

)

Var Test = ADDCOLUMNS(Preptest,“CumSales”,sumx(Preptest,[Sales]),“TT”,CALCULATE(sumx(Preptest,[Sales])),
“EE”, CALCULATE(sumx(Preptest,[sales]),FILTER(Preptest,[index]<=EARLIER([index]))),
“FF”, CALCULATE(sumx(Preptest,[sales]),FILTER(all(‘ZZ DSO4’[Index]),‘ZZ DSO4’[Index]<=EARLIER([index])))
)
Return
test

Sorry - not sure how to format Dax code more easily!
SO now I am at a point where I can get Daily Sales on correct day, insert an index so that date will sort backwards for later earlier calc. I am struggling however to get running totals to build.
Problem is referencing the table pretest to perform EARLIER on Index. “EE” and “TT” an “FF” all return total sales and Not a growing total

HELP!!
Pete

3

@Pete673,

Yes, formatting complex DAX code like this will help enormously. Go to

paste in your code and click “Format” and it will automatically format it for you. Better yet, add this awesome little add-on from @samaguire and you can access DAX CleanUp and all the other Analyst Hub tools from your PBI External Tools menu:

With regard to your measures, EARLIER works fine in a calculated column, but can be very troublesome to get to work properly within a measure. SQLBI and eDNA both recommend retiring EARLIER in favor of an approach using variables and a standard CALCULATE/FILTER construct. @Greg does a great job walking through this issue with tons of examples in his DAX Patterns page on Obtaining Previous Row Value:

I hope this is helpful.

  • Brian
1 Like
4

Hi Brian
Still struggling with this. I am trying to get a running total of sales going in reverse date order (hence the ReverseDateIndex). Can’t see at all where I am going wrong, filters removed on ReverseDateIndex, variable for Max(reversedateindex) are all in play.
SO maybe it has something to do with the fact that I’m trying to do this in a virtual table via a SUMMARIZE. In which case totally stumped.
Fresh PBIX attached and code for dax table generation below.
Thanks for tips on formatting DAX code.

ZZ DSO5 =
VAR myDate = CALCULATE( max( 'Calendar'[Date] ),'Calendar'[Date] = Date( 2020, 3, 31 ) ) 
VAR myDateRange = DATESINPERIOD( 'Calendar'[Date], mydate, -31, DAY ) 
VAR myAccount = "C0057" 
VAR myAccountBalance = CALCULATE(
    [AccountBal],
    FullFile[AccountCode] = "C0057", FullFile[DebtorDate] <= myDate
) 
VAR maxIndex = MAX( 'Calendar'[ReverseDateIndex] ) 
VAR Preptest = SUMMARIZECOLUMNS(
  'Calendar'[Date],
  'Calendar'[ReverseDateIndex],
    myDateRange,
    "Bal", myAccountBalance,
    "Sales", CALCULATE(
        sum( FullFile[Balance] ),
        FILTER( FullFile, FullFile[CDKFile] = "Trans" ),
        FILTER( FullFile, FullFile[DocumentType] <> "P" ),
        FullFile[AccountCode] = myAccount
    ),
    "RunTot", CALCULATE(
        sum( FullFile[Balance] ),
        FILTER( FullFile, FullFile[CDKFile] = "Trans" ),
        FILTER( FullFile, FullFile[DocumentType] <> "P" ),
        FullFile[AccountCode] = myAccount,
        FILTER(
            ALL( 'Calendar'[ReverseDateIndex] ),
          'Calendar'[ReverseDateIndex] < maxIndex
        )
    )
) Return Preptest
```[DebtorsEDNA.pbix|attachment](upload://hpEBUFipjojXfn04GoLbDJy2KK4.pbix) (517.1 KB)
5

@Pete673,

See if this helps:

https://forum.enterprisedna.co/t/calculate-a-reverse-cumulative-total-in-power-bi-using-dax/2936

If not, I’m happy to work on this one with you - just have a tough one in the queue that I’m finishing up, and can tackle this one after that if the video above doesn’t get you to the solution you need.

Hope this is helpful.

  • Brian
6

Hej Brian
They’re all tough ones! Just some are less tough than others.

Pete

1 Like
7

Hi Brian
Video was interesting - as they all are and you can see a use elsewhere.
But I’d overcome the dates backwards thing in the power query piece by placing an index starting at 1 for the highest date increasing by 1 for each day before and am trying to use this for the running tot.

I think my problem is to do with SUMMARIZE and maybe that I’m trying to test the solution with a static DAX table??

Put the step I’m struggling with into context of my end Dynamic Daily Sales outstanding calcn:

  1. Identify previous 365 days from a selected date and Account No
    (I’m working with a Static DAX table, static date and single account just to build the logic).
  2. Identify current balance outstanding
  3. Identify sales by Day
    All above seem to be working fine
  4. Calculate a running total of daily sales going back in time - This is the current challenge!
  5. Calculate current balance minus running total of daily sales
  6. Identify The first day that the calculation in 5 falls below zero
  7. The Date diff between this date and the selected date will be the Debtor days outstanding.

All sounds fantastic until you start to put hands to keyboard and even when you’ve helped with the current problem step, I may not have the skill to complete the exercise. LOL.

I guess if you can get me thru point 4 then I’ll see where I take it.
Thanks ever so much in advance

Pete

8

@Pete673,

Thanks so much for the detailed explanation and the PBIX file - makes providing support much easier. One more request - can you please also post the Excel file with the data for DebtorsEDNA.pbix, in case I need to do some additional power query work?

These are exactly the kind of problems I love. Let me jump on this one as soon as I finalize the solution for @vishy.

Thanks! Hopefully, back to you soon with a proposed solution.

  • Brian
9

@Pete673,

Unless, I’ve misinterpreted something in the original problem explanation, I think this can be solved really directly - no reverse date index, variables or virtual tables necessary. Just a slight wrinkle on the standard cumulative total pattern:

Reverse Cumul Tot = 

CALCULATE(
    [SumBalance],
    FILTER(
        ALLSELECTED( 'Calendar' ),
        'Calendar'[Date] >= MIN( 'Calendar'[Date] )
    )
)

image
image978×567 42.7 KB

I hope this is helpful. Please let me know if this gets you what you need or not.

Thanks. Full solution file attached.

10

Hi @Pete673, I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

11

@Pete673,

I had a little extra time , so I took a run at dynamically calculating items #5-7 on your list above.

image
image998×569 41.1 KB

Here’s the DAX for the earliest date when total balance minus reverse cumulative total hits zero or below:

MinDate = 

CALCULATE(
    MIN( 'Calendar'[Date] ),
    FILTER(
        ALLSELECTED('Calendar'),
        [CurrBal-RevCumul] <= 0 &&
        [SumBalance] <> BLANK()
    )
)

One suggestion – if you’re doing a lot of time intelligence, instead of your calendar table use @Melissa’s awesome date table code. We did a whole video series on leveraging the features of this table to make your time intelligence calculations easier and more powerful. Just the use of the offset fields alone will make the switch worthwhile for you, I think.

I hope this is helpful to you. Revised solution file attached below.

1 Like
12

Hi Brian
Thanks for spending time on this. Unfortunately that’s not quite what I want.
But it has given me some pointers on how to identify the day.

I’m stuck on a payroll project for a couple of days. But your pointers of building the measure with multiple columns in a table and measure branching are probably better than my idea of SUMMARIZE.
Basic stuff really and good to be redirected.

I’ll have a go later in the week at this and come back to you when I hit a problem, if that’s OK?
NB: that was a when, not an if - LOL

Thanks for assist
Pete

13

@Pete673,

Sorry I missed the mark on the solution. If you have a mock-up of what the results should be, I’d be eager to take another run at this.

Otherwise, happy to work w/ you once your payroll project is done.

  • Brian
14

HI Brian
See fresh attached pbix.
What I’m trying to do is work out for any selected date the Daily Sales Outstanding (DSO). This is calculated in financial speak by taking the account balance ( amount owing ) and reducing that balance by each day’s sales before the selected date until it reaches zero. The selected day less the “day it reaches zero” id the DSO.

In the fresh PBIX attached is a tab called Help Please where I’m mocking up the sort of things I need to know to make the end measure work.
DDAcBal gives the account Balance at the maxdate.(This is OK and Working)
AccountBal is a bit redundant in this but breaks the amount owing by docnumber. (OK and Working)
Daily sales shows the sales for each doc
Cum Daily Sales should be working out the running total of the sales going backwards in time.
(This is the piece that is not working).
Once tis works we can identify the zero Day by taking cum Daily Sales from DDAcBal.
In the example pbix this is 04/02/20. This will give a DSO of 56 days from a selected date of 31/3/20.
If the Selected date is 29/2/20 the DSO would be 80 days.

I would like my measure to track over time on any given account or ledger type, so that I can chart it or table it by monthyear. This should be fairly easy once the measure is written.

Looking forward to your thoughts and thanks for your help.

PeteDebtorsEDNA.pbix (519.7 KB)

15

@Pete673,

Thanks very much for the additional explanation and revised file. Will make another run at this tonight and hopefully get back to you soon after that with a working solution.

UPDATE: worked on this for a good while tonight, but still haven’t cracked it. Need to call it a night, but will give it another go tomorrow with fresh eyes.

  • Brian
16

Thanks Brian. You’re just top man!

17

@Pete673,

Well, my friend, I’ve got bad news and good news. The bad news is I could never figure out exactly what the cumulative total of the “sales going backwards in time” looked like, and how that fit into the overall picture. The good news is that I don’t think we need it at all since we can calculate the zero day directly without it using the following two branched measures:

DocDates AcctBal Nonzero = 
IF(
    [AccountBal] > 0,
    SELECTEDVALUE( FullFile[DocumentDate] ),
    BLANK()
)

ZeroDay = 
CALCULATE(
    MINX(
        VALUES( FullFile[DocumentDate] ),
        [DocDates AcctBal Nonzero]
    ),
    REMOVEFILTERS( FullFile[DocumentDate] )
)

Once we have the zero day, calculating the DSO is just a no-frills DATEDIFF measure:

DSO = 
DATEDIFF(
    [ZeroDay],
    [SelectedDate],
    DAY
)

Here are the two test cases you provided in your last explanation to show that the calculations are working properly:

image
image282×568 9.13 KB

image
image278×569 9.29 KB

I’ve enjoyed working with you on this and I hope this gets you what you need.

Full solution file attached below.

– Brian

eDNA Forum DebtorsEDNA Solution2d.pbix (511.4 KB)

1 Like
18

Hi Brian
We’re getting close here. See attached pbix.
Works fine for an individual account at a selected date, but it is not working thru the amount for multiple accounts or the history going back in time.

  1. Table headed Account Code, MonthYear, DSO
    I’m expecting Mar 2020 to read 56 and Feb 2020 to read 80
  2. Table headed Account Code, DSO
    Individual account codes seem fine, but total should look be combining the 2 accounts and working DSO out on the two combined.
  3. Table headed ledgerDesccription
    Beacuse this PBiX only has two accounts with same ledger description, we’d expect whatever is in 2 above combine in the “General Sales Ledger”

Any chance you could take another look??
eDNA Forum DebtorsEDNA Solution2d.pbix (521.2 KB)

Pete

19

@Pete673,

Sure - will take a look this evening after work and get back to you with a revised solution.

  • Brian
20

@Pete673,

I’m still wrestling with this alligator, and the thing I’m really struggling with/stumbling over is this table:

image

Can you help me understand the month and year context? To me it seems that both DSO and zero day have to be determined at the daily level. Given that there are multiple document dates within a given month and year, I’m not sure how to interpret a result that’s aggregated at the month level. To me, it would seem analogous to having a measure that calculates whether a date is a holiday or not, and then imposing an evaluation context of October 2020 on that measure. Does that make sense?

Any clarification you can offer on this issue would help me move forward on a solution.

Thanks, and sorry for the delay.

– Brian