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.
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.
Looks nice. The only question, which value is shown for 04 of Apr, 10 or 30?
- Both? Before delivery and after delivery. At least, that was the intent.
