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.
Hej Brian
They’re all tough ones! Just some are less tough than others.
Pete
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:
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
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.
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] )
)
)
I hope this is helpful. Please let me know if this gets you what you need or not.
Thanks. Full solution file attached.
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!
I had a little extra time , so I took a run at dynamically calculating items #5-7 on your list above.
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.
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
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.
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)
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.
Thanks Brian. You’re just top man!
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:
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)
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.
Any chance you could take another look??
eDNA Forum DebtorsEDNA Solution2d.pbix (521.2 KB)
Pete
Sure - will take a look this evening after work and get back to you with a revised solution.
I’m still wrestling with this alligator, and the thing I’m really struggling with/stumbling over is this table:
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
Hi Brian
Thanks for taking another look. Hope you got some sleep.
The total for the month year visual should ideally be the average of the month DSO’s for each month end date in context of a from/to date slicer.
I can see, having said that, the difficulty in working the DAX thru so that it would run quickly and I think this is a nice to have. More importantly is the issue with getting the average at the same time point across a series of accounts - ie the other part of the problem.
When I responded I though they were the same thing but obviously not.
I’m moving another aspect of this report on to identify overdue amounts at a timepoint/dynamically.
That’s proving a bit of a challenge too! But I’ll keep on it and log another issue if I can’t overcome. It’s interesting how what seems the simplest thing can be difficult and what seems difficult can sometimes be very easy!
For background I work as Finance Director in the Motor trade and have come through a year’s learning curve on Power bi, written reports for Management Accounts, Service job tracking, vehicle sales profitability, service, bodyshop and parts sales tracking. I’d have given up a long time ago without this forum. SO thanks again for your help.
Look forward to your thoughts
Pete
I’m still working this one, but struggling to get a solution. Sorry if I’m being dense here, but when I summarize the data by date, I get this:
GIven that for the latest date in the dataset the account balance remains positive, how can you compute the ZeroDay, and thus the DSO?
I’m sure I’m missing something obvious, but I just can’t see it.
Thanks.
Hi Brian
Thanks for your help again Brian.
I found some time over the weekend to review this and whilst the DAX is working as designed. i think the commercial logic which I was applying was flawed.
So I have reverted to a more traditional approach to calculate DSO based on month summary totals.
I have created a table at time of data refresh with columns for account, balance and sales amounts for the each of the previous 6 months. From this history table I can create a measure to do the job easily up to previous month end, which is fine.
Having put a different solution in place I feel I’ve been wasting your time, but I do always learn a bit more when you respond to my posts. So apologies for a wasted journey, but thank you for imparting some more knowledge on DAX.
Regards
Pete
No apologies needed. I always find your questions interesting and this one was no exception. I learned some things through the process of working through this as well. Just glad you were ultimately able to get to a good solution, even if it took us a while to get there…
Also, were you able to solve the overdue invoice calculation issue you mentioned? I checked the forum and searching on “overdue” produces a lot of solutions related to that question. As always, just give a shout if needed.
