Forum Discussion
DAX: Running Total
Roli_Bird Possibly there is a better way. For instance, by making the sequential date list dynamic. But I didn't go so far. The attached file does what you need.
See if you can get it to work on your end.
Roli_Bird Possibly there is a better way. For instance, by making the sequential date list dynamic. But I didn't go so far. The attached file does what you need.
See if you can get it to work on your end.
Now I have one more question. Why do you use a separate table DateList and not just use the calendar?
To me it looks like that will work too.
With calendar only:
=CALCULATE(
SUM([Quantity]);
FILTER(ALLSELECTED('Calendar'[Date]);
ISONORAFTER('Calendar'[Date];MAX('Calendar'[Date]);DESC)))Instead of with separate DateList:
=CALCULATE(
SUM([Quantity]);
FILTER(ALLSELECTED('Calendar'[Date]);
ISONORAFTER('Calendar'[Date];MAX(DateList[DeliveryDate]);DESC)))What is the difference or why is the additional table DateList required.
As variant (Calendar only)
Total Qty:=SUM( Deliveries[Quantity] ) /// Running Total:=VAR maxDate = MAX ( 'Calendar'[Date] ) VAR minDate = MIN ( 'Calendar'[Date] ) VAR lastDeliverydate = CALCULATE ( MAX ( 'Deliveries'[DeliveryDate] ), ALL ( 'Deliveries' ) ) RETURN IF ( minDate <= lastDeliverydate + 7, CALCULATE ( [Total Qty], Deliveries[DeliveryDate] <= maxDate, ALL ( 'Calendar'[Date] ) ) )with
Patterns are here
SergeiBaklan Thanks for the links to the patterns. I will study them.
What I don't understand, why so complicated?
The following formula seems to work. Without DateList. Only with deliveries and calendar.
RT 2:=CALCULATE( SUM(Deliveries[Quantity]); FILTER(ALLSELECTED('Calendar'[Date]); ISONORAFTER('Calendar'[Date];MAX('Calendar'[Date]);DESC))) +0What am I not seeing?
(Instead of the screenshots, I would like to upload my example file. But I don't know how to do it here.)
Well done!
It looks utterly alien to me, though. I did find my way to
RT:=CALCULATE( SUM([Quantity]), FILTER(ALLSELECTED('Calendar'[Date]), ISONORAFTER('Calendar'[Date],MAX(DateList[DeliveryDate]),DESC)))but I am not sure that I could write anything of this nature. It looks like a sequence of summations, each starting from the top, rather that a rolling accumulation. Is that the case?
I did confirm that I could generate the charts from the source data using Excel 365 dynamic arrays but, since that was not required, I deleted the file.
I did also wonder about a different chart type, namely a scatter chart that uses the vertical error bar to indicate the change in opening to closing balance on a delivery date and the horizontal error bar to bridge between one date and the next. The hope would be to achieve clean steps rather linking values with sloping lines.
PeterBartholomew1 I take no credit for it. This particular one I picked-up from Mynda . Should have mentioned that.
https://www.myonlinetraininghub.com/power-pivot-running-total
Edit: And, in fact, when you add a "Quick Measure" for a running total in Power BI, you get something similar.
Thanks for the clarification. I am still willing to be impressed by you and SergeiBaklan though.
Meanwhile, I might as well post my non-answer to the question. Using 365 and the error bars on a scatter chart I got
from the formula
= LET( deliveredQty, XLOOKUP(date, IF(item=@selected, delivered), qty,0), SCAN(,deliveredQty,LAMBDA(acc,q,acc+q)))A perfectly good answer, just not to this question (grin)!
My hope is that this formula would remain viable even were it accumulating 100,000 values. Three does not exactly push it to the limits.
Hello Riny
Thank you for your solution.
I must have spent a day trying to build the measure without success.
Your measure does exactly what I need.
I will now look at it carefully so that I understand it too. Then I build it into my dashboard, in which the calendar is of course dynamically created in PowerQuery.
Thank you again for your solution.
Roland
