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]
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.!!!!
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.
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] || ServiceRequest[NotClosedAndBlankCloseDate] = 1, 'Date'[Date]
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
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.
What you are asking is described in this article: www.sqlbi.com/articles/a-proposal-for-visual-calculations-in-dax/ You can vote the related idea here: ideas.powerbi.com/ideas/idea/?ideaid=15bd4fbe-829a-4ae2-b093-937b64cf790c
Hi Alberto great video ! I expected the running total to have the filter function like this : CALCULATE [Sales Amount ], FILTER ( ALL(Date), 'Date[Date]
The result is the same, applying a filter over the Date column of a table marked as a date table removes the filter from all the other columns of the same table. It is always better to use a column filter instead of a table filter for performance and for clarity (code is shorter, too).
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 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?
Sales Amount is a measure, which internally has always some aggregation. In this case, Sales Amount is defined as SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
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.
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 is executed in the filter context, FILTER generates a row context, if you don't write CALCULATE the row context is still a row context and not a filter context, so the filter context where MAX is executed is the one of the cell where you execute the entire measure in the report. However, using the VAR is much better and easier to read!
@@SQLBI Thank you very much Alberto! Kindly appreciated! My mind seems to be stuck in some kind of wrong loop about it, I’ll get it eventually. :)) I do understand that FILTER generates (and iterates) row context, and I can clearly see that
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]
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.
You can, but it's going to be extremely expensive. Basically, the running total formula must be computed for each customer and you can use the cumulated formula in the segmentation pattern: www.daxpatterns.com/dynamic-segmentation/
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
Because you want to remove the filter on all the columns of the date table. That is automatic when you filter the Date column in a table marked as a Date table, but in case it is not marked as such, the filter removal makes the DAX measure safer to use. No performance impact for the additional ALL/REMOVEFILTERS.
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. :)
Can you explain YTD calculation, especially date filter condition used inside the filter function --> CummulativeSales = CALCULATE([TotalSales], FILTER(ALLSELECTED(DatesTable),DatesTable[Date]
MAX works in the filter context (ignoring the row context) and gets the maximum date displayed in the current cell of the report (for example, the last day of the month if the cell displays a month). The result of FILTER only includes dates since the first date visible in the entire visual and he filter is applied to the Date column of DatesTable. If DatesTable is marked as a date table (or if the Date column is used in a 1:N relationship) then all the filters on other columns are removed.
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
That argument removes the filter on Date in case you did not mark as date table the table Date and the relationship is not using a Date data type. www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/ www.sqlbi.com/articles/mark-as-date-table/
Removing the filter with the ALL function enables your running total(s) to hold true when the user adds filter context to the date range. For example, adding a date range slicer is quite common. When the user interacts with the date range slicer - adding filter context to view a smaller range of dates for example - you may not want your running total to "drop" the values outside of the range.
Hi , I have tested the date filter slicer. Its showing the same result with and without All function. Its summing of the all previous values even though that dates are not selected Ex date range from Jan'18 till Dec'20 and running Total of sales calculated accordingly I have kept date slicer which filter the date from March'18 to Oct'18. Still report have RT from Jan'18 to Oct'18 ( not started from March'18). Same result shown with and without All function
You'd have to add a column to your date table to specify financial year. You can then add an extra piece to the filter to check the financial year of the dates match, in effect partitioning the table to the financial year in scope.
Look at the article: www.sqlbi.com/articles/computing-running-totals-in-dax/ The last example is what you need, just use the same column (Customer Class) instead of the numeric column (Customer Class Number).
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
Debugging, mainly. When you want to inspect a partial result of a complex expression, you can just replace the variable returned by the measure with whatever variable is computed in the code. When you are done, you know you only need to restore "RETURN Result" as the last line. After a while, you get used to ALWAYS use RETURN Result as the closing of any measure.
@@albertoferrari6893 yes!, you're good in DAX 🤣 of course you're a master, I'm just joking. Keep going Alberto, your videos are very good and helpful , i learned lot from you and Marco 👍
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
@@SQLBI thanks for the Quick reply... what I'm trying to do is an inventory shortage model. please see the below fig. here ( balance= inventory - Running Total )by Raw wise my plan is to subtract Running total from inventory to get balance(FIFO System). my limit is I can't calculate Running total within a day(ex 11/17/2020). it gives 19 for all records which date is 11/17/2020 instead of giving a breakup as below . in my report there are many dates, many orders numbers which need to be sorted. Order Date Order No Order Qty Running otal inventory Balance 11/17/2020 A 5 5 15 10 11/17/2020 B 10 15 15 0 11/17/2020 C 4 19 15 -4
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]
I have a measure, that i am trying to calculate its running total. The measure, lets call it C, is equal to the ratio of two other measure A and B, i.e. C = A/B. When trying your DAX formula and including C instead of Sales, i am not getting the correct answer. here is my DAX formula, running total measure = var maxdate = max('Table'[Year]) var result = CALCULATE([C],'Table'[Year]
@@SQLBI thanks for the reply, we rarely use the accumulated total that passes the year....hopefully with your book “The Definitive Guide..” things will get more clear about how filtering works!
@@SQLBI I have placed variables in my code for all P&L and balance sheet calculations, reducing total filter on tables, I think it is 60% faster ... thank you
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]
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.!!!!
THANK YOU : you have no idea how long I was looking for this explanation
Your videos are great! Currently going through the mastering DAX course.
mind blowing explanation - thank you for the education. You really explained so well diff between YTD and running total.
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.
Simple and to the point! Thank you.
Thanks for expanding on non-date RT. Much appreciated
Nice job explaining this!
Useful, and explained so coherently. Thank you!
Simple and focused, thank you
Thank you! Great video!
nice video. i learn allot from you.
Alberto, sei un mito!
thank you thank you thank you ferrari I love you please come to Brazil
Thank you so much!
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] || ServiceRequest[NotClosedAndBlankCloseDate] = 1,
'Date'[Date]
Marrrrioo... Well explained!!!
TOP!!!!!!!#powerbinareal
Nice one..
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
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
Thanks allot
What if we only wanted the running total of visible dates? Wouldn't ALLSELECTED('Dates') be a more useful filter?
Hi SQLBI,
What is the evaluation context of this Filter part
FILTER (
ALL('Date'),
'Date'[Date]
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.
What you are asking is described in this article: www.sqlbi.com/articles/a-proposal-for-visual-calculations-in-dax/
You can vote the related idea here: ideas.powerbi.com/ideas/idea/?ideaid=15bd4fbe-829a-4ae2-b093-937b64cf790c
I'm a newbie, and my running total is the same... I believe you have a video somewhere that explains the Dax Code please?
How can we do this in calculated column...
Please explain
Hi Alberto great video ! I expected the running total to have the filter function like this :
CALCULATE
[Sales Amount ],
FILTER (
ALL(Date),
'Date[Date]
The result is the same, applying a filter over the Date column of a table marked as a date table removes the filter from all the other columns of the same table. It is always better to use a column filter instead of a table filter for performance and for clarity (code is shorter, too).
How to show as % of running total ?
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 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?
It depends, you should analyze the query plan to establish the reasons of the slowness.
In the calculate statement, I noticed that sales amount was not aggregated using for example, a sum function. That's not necessary?
Sales Amount is a measure, which internally has always some aggregation. In this case, Sales Amount is defined as SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
No, because [Sales Amount] is a measure. The aggregation formula is already inside the measure.
Thanks much.
what if i want to filter it by date using slicer
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.
When you say use all() to remove filter on date. What is this just for the internal context of the calculate?
Yes, inside CALCULATE ALL removes any filter. That is required, to see dates that would otherwise be filtered by the visual
Thanks, since the max date is defined in the VAR before the ALL this makes sense, bit of a catch ALL
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 is executed in the filter context, FILTER generates a row context, if you don't write CALCULATE the row context is still a row context and not a filter context, so the filter context where MAX is executed is the one of the cell where you execute the entire measure in the report. However, using the VAR is much better and easier to read!
@@SQLBI Thank you very much Alberto! Kindly appreciated! My mind seems to be stuck in some kind of wrong loop about it, I’ll get it eventually. :)) I do understand that FILTER generates (and iterates) row context, and I can clearly see that
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]
Try this:
VAR MaxVal = MAX(Table1[Val])
CALCULATE (
SUM ( Table1[Val] ),
Table1[Val]
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.
You can, but it's going to be extremely expensive. Basically, the running total formula must be computed for each customer and you can use the cumulated formula in the segmentation pattern: www.daxpatterns.com/dynamic-segmentation/
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
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
Because you want to remove the filter on all the columns of the date table. That is automatic when you filter the Date column in a table marked as a Date table, but in case it is not marked as such, the filter removal makes the DAX measure safer to use. No performance impact for the additional ALL/REMOVEFILTERS.
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. :)
You might need that if you add other filters/slicers in the report!
Can you explain YTD calculation, especially date filter condition used inside the filter function --> CummulativeSales = CALCULATE([TotalSales], FILTER(ALLSELECTED(DatesTable),DatesTable[Date]
MAX works in the filter context (ignoring the row context) and gets the maximum date displayed in the current cell of the report (for example, the last day of the month if the cell displays a month). The result of FILTER only includes dates since the first date visible in the entire visual and he filter is applied to the Date column of DatesTable. If DatesTable is marked as a date table (or if the Date column is used in a 1:N relationship) then all the filters on other columns are removed.
Great question. Great answer. Thanks a lot!
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
That argument removes the filter on Date in case you did not mark as date table the table Date and the relationship is not using a Date data type.
www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/
www.sqlbi.com/articles/mark-as-date-table/
What if I do not remove the filter on the date table using the ALL function?
Removing the filter with the ALL function enables your running total(s) to hold true when the user adds filter context to the date range. For example, adding a date range slicer is quite common. When the user interacts with the date range slicer - adding filter context to view a smaller range of dates for example - you may not want your running total to "drop" the values outside of the range.
Hi ,
I have tested the date filter slicer. Its showing the same result with and without All function.
Its summing of the all previous values even though that dates are not selected
Ex date range from Jan'18 till Dec'20 and running Total of sales calculated accordingly
I have kept date slicer which filter the date from March'18 to Oct'18. Still report have RT from Jan'18 to Oct'18 ( not started from March'18). Same result shown with and without All function
@@andremunoz3939Can you please explain.
Get rid of all the filters on the date? Why would there be filters on the date?
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
You'd have to add a column to your date table to specify financial year. You can then add an extra piece to the filter to check the financial year of the dates match, in effect partitioning the table to the financial year in scope.
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
I need a running total by alfabetical sorting, ¿Can you help me?
Look at the article: www.sqlbi.com/articles/computing-running-totals-in-dax/
The last example is what you need, just use the same column (Customer Class) instead of the numeric column (Customer Class Number).
How MAX('Date[Date') can ignore the filter context?
It does not. That is the MAX in the current month
More interesting question is how MAX('Date[Date') can ignore the ROW context of the Filter?
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
Debugging, mainly. When you want to inspect a partial result of a complex expression, you can just replace the variable returned by the measure with whatever variable is computed in the code. When you are done, you know you only need to restore "RETURN Result" as the last line.
After a while, you get used to ALWAYS use RETURN Result as the closing of any measure.
@@albertoferrari6893 yes!, you're good in DAX
🤣 of course you're a master, I'm just joking.
Keep going Alberto, your videos are very good and helpful , i learned lot from you and Marco 👍
@@albertoferrari6893 thanks
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
You can use ISONORAFTER to compare multiple columns (date, then order) in the filter condition.
@@SQLBI
thanks for the Quick reply... what I'm trying to do is an inventory shortage model. please see the below fig. here ( balance= inventory - Running Total )by Raw wise
my plan is to subtract Running total from inventory to get balance(FIFO System).
my limit is I can't calculate Running total within a day(ex 11/17/2020). it gives 19 for all records which date is 11/17/2020 instead of giving a breakup as below
. in my report there are many dates, many orders numbers which need to be sorted.
Order Date Order No Order Qty Running otal inventory Balance
11/17/2020 A 5 5 15 10
11/17/2020 B 10 15 15 0 11/17/2020 C 4 19 15 -4
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]
Your code works, but it's much slower and doesn't work for non-additive measures.
@@SQLBI Thank you.
I have a measure, that i am trying to calculate its running total. The measure, lets call it C, is equal to the ratio of two other measure A and B, i.e. C = A/B. When trying your DAX formula and including C instead of Sales, i am not getting the correct answer. here is my DAX formula,
running total measure =
var maxdate = max('Table'[Year])
var result = CALCULATE([C],'Table'[Year]
First question should be: What is DAX?
It is simple but it does not work in Power pivot...the running total resets.
Can you provide more details?
@@SQLBI Hello, I have two tables sales and calendar, the measure is:=calculate(sumx(sales[Price]*sales[Qty]), filter(calendar,calendar[date]
The filter is wrong, use this:
calculate(sumx(sales[Price]*sales[Qty]), filter(ALL(calendar),calendar[date]
@@SQLBI thanks for the reply, we rarely use the accumulated total that passes the year....hopefully with your book “The Definitive Guide..” things will get more clear about how filtering works!
@@SQLBI I have placed variables in my code for all P&L and balance sheet calculations, reducing total filter on tables, I think it is 60% faster ... thank you