Forum Discussion
Filtercontext in running total explained
Hello,
I know its a common formula how to compute the running total. I just don't understand why it works and always took it for granted. For example we have this very standard measure, which is computing the running total of the year and also to a specific point in time :
RunningTotal=
Calculate (
Sales[Value]),
Filter ( 'Calendar'),
'Calendar' [Year]= MAX( 'Calendar' [YEAR]) &&
'Calendar' [Date]<=Max ('Calendar' [Date] )
)
)
| Date | Sales | RunningTotal | MAX('Calendar'[Year] |
| 2017-01-01 | 100 | 100 | 2023 |
| 2017-01-02 | 500 | 600 | 2023 |
| 2017-01-03 | 2000 | 2600 | 2023 |
| 2017-01-04 | 3000 | 5600 | 2023 |
| 2017-01-05 | 100 | 5700 | 2023 |
| 2017-01-06 | 400 | 6100 | 2023 |
| 2017-01-07 | 300 | 6400 | 2023 |
| 2017-01-08 | 150 | 6550 | 2023 |
| 2017-01-09 | 90 | 6640 | 2023 |
| 2017-01-10 | 100 | 6740 | 2023 |
| Total | 6740 | 6740 |
4 Replies
Applicable88 Filter is an iterator.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
parry2k , I know Filter goes row by row. But the iterator function is not explaining the fact, that it's not using the MAX Year of that dataset, but the MAX Year in that filter context of the computed row.
So I'm pretty sure its not the filter()- function which makes it happen here.
Applicable88 you answered your own question, think, if it goes row by row then on each row it returns the max value of that row, if you are using MAX ( Year ), it is against full table.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi Applicable88
The measure you provide have the DAX max(), when it calculates in a table visual, is the same as selectvalue().I'll provide some data that you can see more visually.
I create a measure : M_max= MAX('Table'[value])
For the first line, the max() calculate the column form a table (Table of the process, as the following show ) where index_1 = 1.
Only two rows and the maximum value is 2,so the result is 2.
For the second line, the process table is like this
. So the maximum value is 3. And so on….
The filter context is the index_1 to filter the table which will be input into the measure to calculate.
In your caes, MAX('Calendar'[Year]) , if it is a measure in table visual, the result should be 2017. If you want to get 2023,maybe try the next measure, this will calculate maximum year of all the column:
Measure = MAXX(ALL(‘Calendar’),[Year])
If I misunderstood you , please let me know.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
