Cumulative sum by dates by persons

[Raphaël]

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.

[Datatouille · Accepted solution]

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.

[Raphaël]

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 

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 ?

[Raphaël]

Hi again  @Datatouille,

I've read the entire article and now understand the meaning of row & context filter.

So I've understood the following from below formual :

FILTER (ALL ('Query'[Date] ),'Query'[Date] <= MAX ( 'Query'[Date]))

ALL ('Query'[Date] ) : will bring me all [Date] from 'Query' depending on the filter context (i.e. the row where the value is calculated, so here, one person).

This part, I still not get it :

'Query'[Date] <= MAX ( 'Query'[Date])

I know it does the work properly but

how power bi knows from which date to which date filter my data to give me the running count I'm expecting ?

[Datatouille]

@Raphaël

So, As I mentioned earlier, at this stage, the Filter function is iterating over each row of 'Query' Table in the current filter context.

This part:

'Query'[Date] <= MAX ( 'Query'[Date])

'Query[Date]' uses the row context generated by Filter. You compare each date (visible in the current filter context) to MAX('Query'[Date]).

But what is MAX('Query'[Date]) doing ? It is computing the maximum date in the current filter context.

So in your case, your Max('Query'[Date] ) is always equal to the date in your pivot table. Filter only keeps all the dates (currently visible in the filter context) which are less or equal to this MaxDate.

But be careful, if you have several years in your model, this calculation will compute 'cumul' from the very beginning of the calendar. If you want to perform a genuine Year To Date calculations.

You can use this pattern:

1. Calculate ( [Measure] , DatesYTD ( Calendar[Date] )

or

2. TotalYTD ( [Measure] , Calendar[Date] ) 

I encourage you to use 1. because you can change the year end date with the 2nd (optional) parameter of DatesYTD function (very convenient when you are not computing values on calendar but fiscal years for example) and it materialises the calculate !

[Anonymous]

Hi,
I am looking for Cumulative comparision year over year.
I have Monthly data from Dec 2016 to Nov 2018, I want to compare Last year cumulatives to this year cumulatives.
However the Measure below is calculating for the entire two year period.
Cumulative Amount Paid Measure =
CALCULATE (
    SUM ( DuesPayment[Paid Amt] ),
    FILTER (
        ALLSELECTED('DuesPayment' ),
        'DuesPayment'[Weeks Out] <= MAX ( DuesPayment[Weeks Out] )
Can you explain how this works:
1. Calculate ( [Measure] , DatesYTD ( Calendar[Date] )
or
2. TotalYTD ( [Measure] , Calendar[Date] )

Thanks a lot,

VG

[Datatouille]

Yes sure, I just sent you a private message !