Forum Discussion
DAX: Running Total
Hello, everyone. I hope someone can help me.
In a small example I try to describe my problem. These are my data. They show at wich date how many pieces of an article are delivered.
I would like to display the inventory in a chart like this:
I need to calculate the running total in a measure. With my RunningTotal-measure I get the following result.
To display the chart I need the following result. How can I calculate not only the black but also the red numbers in a measure?
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.
12 Replies
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
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.
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
