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.)
Main difference is how you'd like to show date axis. Usually Date/Calendar table is generated starting from Jan 01 for the year of min date in all tables of the model and ending by Dec 31 for the year of last date.
First, Deliverables could be not only table in model with dates. Second, your data is filled for April only. With ISONAFTER() only you show scale from Apr to Dec. If that's the goal that's okay. If to show only from min to max dates with some gap bit more complex filter is required.
Another point is using of VAR (definition of variable). Of course, you may ignore them putting everything in one formula. However, not discussing details of evaluation and ability to avoid such function as EARLIER, code with variables is better for maintenance and understanding. Even if it is longer.
In attached are charts with both measures, you may compare.
Finally, that's definitely your choice what to use. Both work, the rest depends on goals, what exactly you'd like to show and what is more comfortable for you in maintenance.
And cosmetic - that's best practice to hide from client tools Many side of relationship, allows to avoid errors, especially in complex models and same names in different tables.
