Forum Discussion
Cumulative total query
I actually went through on some of my own data to create a cumulative total in this manner (since the example given is not exactly the way I would do it) and I think the example might be wrong. Have you actually implemented this cumulative total on the sample data they provide? I would be interested if it does work. I'm sure there's some way to do it the way they say using RELATED, but when I started from scratch, I ended up with this:
Cumulative Amt = CALCULATE(SUM('Sales'[Quantity]), FILTER(ALL('Sales'), 'Sales'[Date] <= MAX('Sales'[Date]) ))
The big difference is that I'm not filtering the Date table, I'm filtering the Sales table. However, I think this example is still an example of what you find confusing. There's nothing special about the MAX function. It could be FIRSTNONBLANK, SELECTEDVALUE, MIN, or any other expression that defines what the "previous" values for the cumulative total are. What is special is the FILTER function.The filter expression is actually iterated against every row in the table (in this case ALL('Sales')) to see if it evaluates to true. It's checking that for each row the 'Sales'[Date] is less than or equal to the value of "MAX('Sales'[Date])". It's my understanding that because your MAX expression doesn't have a new context explicitly defined, it evaluates once at a level out, essentially evaluating against the data before the ALL is applied.
I've looked over documentation to see if there's anywhere that the context switch is explicitly called out, or articles that explain it, but I can't seem to find any. I've never questioned it, because how else would you be able to filter a table based on a current value? The automatic context switching is incredibly useful and feels natural to me, so I feel like I can't explain further. Maybe somebody like marcorusso or Greg_Deckler might be able to give a better explanation on how this works.
So this works because you have your dates and Sales data in different tables.
The first part of the CALCULATE expression is easy. You want the sum of all values in Sales[Quantity].
The second part is creating the filter. Any filters applied here override current filters. However, this is only overriding filters on the 'Date' table. So what happening is the FILTER is starting with ALL 'Date' values, and then filtering to only return rows where the [Datekey] is <= MAXimum [Datekey]. The trick here is that MAX('Date'[Datekey]) is operating at the original context, while defining the new context.
So this will keep any filters from other tables (like Sales category), but it will return all values where the date is earlier than the current context's date.
Dealing with headaches like this, where you're not sure why MAX(Datekey) works, is why I prefer to use SELECTEDVALUE instead of MAX. It returns a blank if you don't have a single selection for that value, and works perfectly when your context is correct.
Thanks Cmcmahan
Why does 'Date'[Datekey] operate in the new context but MAX('Date'[Datekey]) operate in the original context? Is there something special about the MAX function?
Thanks,
CM
I actually went through on some of my own data to create a cumulative total in this manner (since the example given is not exactly the way I would do it) and I think the example might be wrong. Have you actually implemented this cumulative total on the sample data they provide? I would be interested if it does work. I'm sure there's some way to do it the way they say using RELATED, but when I started from scratch, I ended up with this:
Cumulative Amt = CALCULATE(SUM('Sales'[Quantity]), FILTER(ALL('Sales'), 'Sales'[Date] <= MAX('Sales'[Date]) ))
The big difference is that I'm not filtering the Date table, I'm filtering the Sales table. However, I think this example is still an example of what you find confusing. There's nothing special about the MAX function. It could be FIRSTNONBLANK, SELECTEDVALUE, MIN, or any other expression that defines what the "previous" values for the cumulative total are. What is special is the FILTER function.The filter expression is actually iterated against every row in the table (in this case ALL('Sales')) to see if it evaluates to true. It's checking that for each row the 'Sales'[Date] is less than or equal to the value of "MAX('Sales'[Date])". It's my understanding that because your MAX expression doesn't have a new context explicitly defined, it evaluates once at a level out, essentially evaluating against the data before the ALL is applied.
I've looked over documentation to see if there's anywhere that the context switch is explicitly called out, or articles that explain it, but I can't seem to find any. I've never questioned it, because how else would you be able to filter a table based on a current value? The automatic context switching is incredibly useful and feels natural to me, so I feel like I can't explain further. Maybe somebody like marcorusso or Greg_Deckler might be able to give a better explanation on how this works.