Computing a running total in DAX

Why did I have to add the .date to make mine work? Without it, it wouldn't give me a cumulative it would just total the month and start over at the next month.

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

Get rid of all the filters on the date? Why would there be filters on the date?

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

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

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 ?

I wish i understant it. Even after a dax bible i still dont ubderstand the way calculate and all replaeces filter context and the still works with it

Ciao,
I've watched this several times and even though it is how I've always approached RTM's, I wondered if you had any view on feeding a filtered table to SUMX to iterate over;
RTM :=
VAR mdate =
MAX ( Tsales[Date] )
RETURN
SUMX ( FILTER ( ALL ( Tsales[Date] ), Tsales[Date] <= mdate ), [Trevenue] )


I actuallly find this eaier to (sort of ) understand.
BTW, love the face that you forget the RETURN, it's something I do so often.

Can someone tell me why ALL ('Date') is included in this Measure? When I have that or don't have that in my measure it seems to make no difference. Thanks

Thanks allot

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

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.!!!!

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?

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?

Sir, How to do summation of first 3 numbers in a series in power bi....?
Series: {5, 7, 2,3,9,10,2}
Desired sum: 5+7+2

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

Thank you Very very much. First Varieblated expression excutes ones and it becames as constant. This part is diffcult to undurstanding to me. How it works.
For exmple I have such Table
Transit / Days / Result(needed column)
1Tranzit 3 0
2Tranzit 5 2
3Tranzit 8 6
It meens=First transit takes 3 days from second Transit. Second transit was planed for 5 days, becouse of first transit the second transit in real remain 2 days not 5days (3-5=2) Then this 2days take from 3Transit 2days and there remains 6day not 8.
Planed days not real days It's just planed days . Fact days is Resalt(Column). I need culculate the risk from bilion rows. I did all my best but could not do it

What if I do not remove the filter on the date table using the ALL function?

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)

Nice one..

Thanks for expanding on non-date RT. Much appreciated

I need a running total by alfabetical sorting, ¿Can you help me?

What would be the best way to calculate cumulative and 12 month rolling totals by customer? I need to maintain and then aggregate the by-customer amounts for customer segmentation. It seems that this can't be a measure because the customer-level segmentation needs to be maintained and then aggregated. For example, create "small customer" below a certain sales amount for cumulative or rolling 12 month. Then aggregate total of the small customer sales amounts.

what if i want to filter it by date using slicer

I like the way you explained it! I did the same in my model but I faced with performance issue which showed slow DAX query (7733 ms). Is it a way how to get the same result of running total but with better Dax query performance?

How can we do this in calculated column...
Please explain

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

First question should be: What is DAX?

Thanks Alberto.
Quick question.
We have learned CALCULATE function overrides all the existing filters and sets its own filter given in its parameter.
then why are we using ALL function here, whose function is the same to return a complete table removing all the filters
If CALCULATE already doesn't respect any filters, why are we still using the ALL function to remove the existing filter in the Date Table?
Thanks in advance

Alberto, sei un mito!

It is simple but it does not work in Power pivot...the running total resets.

Is ALL really required in these examples?
In my test without using ALL('Date' / ALL(Customer[Customer Class]), the CALCULATE filters overwrites the incoming Month filter from the rows, and the running total gives the same result as when I use ALL.
But mayby best practice?
Regards, love to learn from your videos, blogs etc. :)

Marrrrioo... Well explained!!!

Thank you so much!

Hi can you give me a hint to calculate the Running total with duplicate dates. (example: when there are many orders per one day) Ex:
Date Order Order Qty Expected running Total
11/17/2020 A 5 5
11/17/2020 B 10 15
11/17/2020 C 4 19

Can you explain YTD calculation, especially date filter condition used inside the filter function --> CummulativeSales = CALCULATE([TotalSales], FILTER(ALLSELECTED(DatesTable),DatesTable[Date] <= MAX(DatesTable[Date])))

Can you please explain how DatesTable[Date] <= MAX(DatesTable[Date] takes all the dates before current row context, shouldn't it take max date that is last date in table because we use ALLSELECTED(DatesTable) meaning row context in the table visual is removed and all the dates are taken. I understand what i think is wrong but can you explain what is happening actually.

Hi Alberto great video ! I expected the running total to have the filter function like this :
CALCULATE
[Sales Amount ],
FILTER (
ALL(Date),
'Date[Date] <= MaxDate
)
)
Was FILTER removed for aesthetic and is it the same result ? or omitting filter gives better performance ?

Thank you! Great video!

Simple and focused, thank you

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.

Hi Alberto/ Russo , thanks for uploading valuable video. Please let me know how to get running total for financial year. ( Jul to June)

Thanks
Imran

How to show as % of running total ?

Useful, and explained so coherently. Thank you!

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

Simple and to the point! Thank you.

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.

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

nice video. i learn allot from you.

Thanks Alberto.
Quick question.
I noticed you created a VAR for Result rather than put after RETURN the all function. Why did you do that?
Correct way to write Dax expression using variables? Better performance? VAR trigger the CALCULATE function save the result and then measure just display the outcome rather than trigger the CALCULATE function?
But then, on the second measure CALCULATE function is after result. So, not sure which one is the best way to do it?
Thanks