Computing a running total in DAX

[luqmanhakimharahap]

Wow.. I just learn power BI for about a month, and got stumbled upon this problem. This is very helpful!!

[rajeshbusiness]

Thanks a lot. I spent hours trying to figure out an issue myself. I was pretty close but couldn't find a solution and it was driving me crazy. Watched your video and I was able to solve in less than 5 mins.. Love it.!!!!

[IgorAntarov]

Thank you, this video saved my day! I had to modify the code slightly (by adding SUM()) to calculate form a column and not from measure:
VAR MaxDate = MAX ('Date'[Date])
VAR Result =
CALCULATE(
SUM(Projects[Sales]),
'Date'[Date] <= MaxDate,
ALL ('Date')
)
RETURN Result

[kanidarck]

THANK YOU : you have no idea how long I was looking for this explanation

[analistax]

Great !! This type of calculation is also very common in financial investments, in which it is necessary to be aware of the amount applied and returned until that last date.

[drivetrainerYT]

Thanks for expanding on non-date RT. Much appreciated

[devengileo]

mind blowing explanation - thank you for the education. You really explained so well diff between YTD and running total.

[alfredsfutterkiste]

Your videos are great! Currently going through the mastering DAX course.

[sjxxxnn]

Hi Alberto,

First of all, thank you for your work.
I'm new to Power BI, now I'm working on the report that should use RT.
I modified measures to use the same logic as in your example and it works fine, but after some time I received a new requirement to add the filter to the measures. This additional filter is created based on the Status field from another table. Once I add it my running total doesn't work, can you please help with this?


Estimated Sales WIP =
VAR SelectedDate = MAX('Date'[Date])
VAR Result =
CALCULATE(
[Estimated Sales],
SelectedYear < ServiceRequest[Closed Date] || = 1,
'Date'[Date]<=SelectedDate)
RETURN Result

Regards,
Vitalii

[kusalamarasena]

Hi SQLBI,
What is the evaluation context of this Filter part
FILTER (
ALL('Date'),
'Date'[Date] <= 2. MAX('Date'[Date])
))

Is MAX('Date'[Date]) not affected by ALL('Date')? (if it is, then it should give max date of whole date table)

And is Date[‘Date’] column not affected by initial filter context(here Rows of the visual)

[rafaelvargas]

I come from the world of SAP BI and now that I'm learning DAX, I miss some features that seemed simple in WebIntelligence and aren't so much in PowerBI. For example, it would be simpler to create a single measure called "Sales RT" and depending on the context in which it is used, it is calculated automatically, without having to create a variable for date, another by category, another by country, etc. and thus prevent the model from filling up with so many DAX variables. It would be great to have a parameter in the CALCULATE(RunningSum([Sales Amount]), CurrentConext) and this internally to do the rest. If a special calculation is desired, then a variable is created with the context to evaluate.

[vasileiosmanasas]

What if we only wanted the running total of visible dates? Wouldn't ALLSELECTED('Dates') be a more useful filter?

[mikeycbd]

I'm a newbie, and my running total is the same... I believe you have a video somewhere that explains the Dax Code please?

[MatBR]

Hey, this video was useful thanks! But id also like to know how to this exact same thing with for example, total sales per product

[ZenOre]

Useful, and explained so coherently. Thank you!

[markocapan]

Thank you Alberto, for this explanation. Well this one makes sense since you’re using VAR MaxDate which saves outer value.
But in your book which I purchased (Definite Guide to DAX – 2nd edition), you gave a perplexing alternative example – which works (and can be found on internet too for topic of running total), but I simply can’t grasp how it’s possible. Excerpt: FILTER(ALL(‘Date’[Date]), ’Date’[Date] <= MAX(‘Date’[Date])). How is it possible that MAX ignores row context of a FILTER and refers to outer value within FILTER?

[gayathriparameswaran]

this is not working when calculating the running total of a measure calculated for % of total hours (tot.Hours/Quantity).
Can we calculate running total for measures ?

[pawlowski]

In the calculate statement, I noticed that sales amount was not aggregated using for example, a sum function. That's not necessary?

[jakeduddy]

When you say use all() to remove filter on date. What is this just for the internal context of the calculate?

[NasirUddin-opit]

Hi, i have a data set of 17mio rows where there is not date column. I've written a DAX measure for running total of the values. Same measure works file with small data set But it takes huge time to execute in this 17mio data set.
DAX is
CALCULATE(
SUM(Table1[Val]),
FILTER(ALL(Table1), Table1[Val]<=MAX(Table1[Val])),
VALUES(Table1[SKU Code]))

Anything important that i'm missing?