Forum Discussion
Cumulative sum by dates by persons
Dear all,
I am trying to build a cumulative sum by date and by person but can't figure out how to do that. The only thing I suceed to do is a simple cumulative sum by date.
Let me explain with an example.
Here is my dataset :
| Date | Persons | Indicator |
| 01/02/2017 | Mick | 1 |
| 01/02/2017 | Mick | 1 |
| 01/02/2017 | Mick | 1 |
| 01/02/2017 | David | 1 |
| 02/02/2017 | David | 1 |
| 03/02/2017 | David | 1 |
| 03/02/2017 | Mick | 1 |
With below formula here is what I get :
Cumul =
CALCULATE (
SUM ('Query'[Indicator]),
FILTER (ALL ('Query' ),'Query'[Date] <= MAX ( 'Query'[Date]))
)| Date | Persons | Indicator | Cumul |
| 01/02/2017 | Mick | 1 | 1 |
| 01/02/2017 | Mick | 1 | 2 |
| 01/02/2017 | Mick | 1 | 3 |
| 01/02/2017 | David | 1 | 4 |
| 02/02/2017 | David | 1 | 5 |
| 03/02/2017 | David | 1 | 6 |
| 03/02/2017 | Mick | 1 | 7 |
But what I want is :
| Date | Persons | Indicator | Cumul |
| 01/02/2017 | Mick | 1 | 1 |
| 01/02/2017 | Mick | 1 | 2 |
| 01/02/2017 | Mick | 1 | 3 |
| 01/02/2017 | David | 1 | 1 |
| 02/02/2017 | David | 1 | 2 |
| 03/02/2017 | David | 1 | 3 |
| 03/02/2017 | Mick | 1 | 4 |
I have an other question,
I've found the Cumul query on this forum but I didn't exatcly understand how it's working and how the filer part is working, can someone explain it to me ?
Thank you in advance for your help.
I wish you a nice day.
Hi Raphaël
I think you are right to ask not only the answer but also the logic running behind DAX !
I guess 'Persons' column also comes from your 'Query' Table, right ? In that case, try:
Cumul = CALCULATE ( SUM ('Query'[Indicator]), FILTER (ALL ('Query'[Date] ),'Query'[Date] <= MAX ( 'Query'[Date])) )Filter (Table, filter...) is an iterator function. It iterates over each row of the Table (1st argument) in the current filter context and evaluates the filter condition.
And here is the explanation in your case:
The ALL function basically ignores the current filter context.
All(Table) ignores any filters which would come from this 'Table'. This is why your formula isn't computing what you want here.
By writing All(Query), you are ignoring the filter coming from date (which is right here because you want to do a running total) but also the filter coming from 'Persons' (which is NOT what you want here) or any other columns from 'Query' Table that you would potentially bring in your pivot table/slicers, etc.
All accepts a Table or a column as an argument.
My suggestion is based on All(Query[Date]) which allows you to ignore the filter context only coming from [Date] column of Query Table.
6 Replies
- Datatouille
Solution Sage
Hi Raphaël
I think you are right to ask not only the answer but also the logic running behind DAX !
I guess 'Persons' column also comes from your 'Query' Table, right ? In that case, try:
Cumul = CALCULATE ( SUM ('Query'[Indicator]), FILTER (ALL ('Query'[Date] ),'Query'[Date] <= MAX ( 'Query'[Date])) )Filter (Table, filter...) is an iterator function. It iterates over each row of the Table (1st argument) in the current filter context and evaluates the filter condition.
And here is the explanation in your case:
The ALL function basically ignores the current filter context.
All(Table) ignores any filters which would come from this 'Table'. This is why your formula isn't computing what you want here.
By writing All(Query), you are ignoring the filter coming from date (which is right here because you want to do a running total) but also the filter coming from 'Persons' (which is NOT what you want here) or any other columns from 'Query' Table that you would potentially bring in your pivot table/slicers, etc.
All accepts a Table or a column as an argument.
My suggestion is based on All(Query[Date]) which allows you to ignore the filter context only coming from [Date] column of Query Table.
Hi Datatouille,
Thank you for your feedback. I confirm your proposition is the right one and I thank you for this.
Thank you also for your explanation,
I didn't have enought time to read entirely the link about row & filter context (which is interesting) but I'll continue to read it tomorrow and ask you more questions about it :smileyhappy:
I've also noticed you're french (as I am), may we continue in french (not sure this is allowed here) to ensure to myself a better understanding ?
- DatatouilleYes sure, I just sent you a private message !
