DAX running total that does not recalculate at the end of a period or group.

[dicken]

HI,
I have a simple running total measure ;

RTM:=VAR mdate = MAX(Table2[Date]) 
VAR anser = 
CALCULATE([tSales],FILTER(ALL(Table2[Date]),Table2[Date]<= mdate ) )
RETURN anser

but when I put this into a pivot table where the dates are groped by Month the accumulative pricess restarts at the end of each monht,
if it's not grouped it just carries on, is there a way to cause it to ignore the grouping. So for screen short below the first figure for March would be 64.
Richard.

 

[dicken]

HI,
I have a simple running total measure ;

RTM:=VAR mdate = MAX(Table2[Date])
VAR anser =
CALCULATE([tSales],FILTER(ALL(Table2[Date]),Table2[Date]<= mdate ) )
RETURN anser

but when I put this into a pivot table where the dates are groped by Month the accumulative pricess restarts at the end of each monht,
if it's not grouped it just carries on, is there a way to cause it to ignore the grouping. So for screen short below the first figure for March would be 64.
Richard.


View attachment 68723

This is cross questioned on Excel Forum ;

DAX running total that does not recalculate at each group

Hello all, I have previously asked this question on Mr Excel but had no answer; https://www.mrexcel.com/board/threads/dax-running-total-that-does-not-recalculate-at-the-end-of-a-period-or-group.1209902/ The quesition is I have a simple running total measure...

www.excelforum.com

 

[RoryA]

The grouping creates a new field which has its own filter context and your ALL doesn't cater for it, so you'd need something like:

=VAR sdate = MAX(ForumTotals[Date])
RETURN 
CALCULATE([tsale],FILTER(ALL(ForumTotals[Date],ForumTotals[Date (Month)]),ForumTotals[Date]<= sdate) )

 

[dicken]

The grouping creates a new field which has its own filter context and your ALL doesn't cater for it, so you'd need something like:

=VAR sdate = MAX(ForumTotals[Date])
RETURN
CALCULATE([tsale],FILTER(ALL(ForumTotals[Date],ForumTotals[Date (Month)]),ForumTotals[Date]<= sdate) )

I've had a couple of responses but not had time to try any out, but will get back to you once I have.

RD

 

[dicken]

The grouping creates a new field which has its own filter context and your ALL doesn't cater for it, so you'd need something like:

=VAR sdate = MAX(ForumTotals[Date])
RETURN
CALCULATE([tsale],FILTER(ALL(ForumTotals[Date],ForumTotals[Date (Month)]),ForumTotals[Date]<= sdate) )

Thanks, that works fine, I like it very much as it might , as thinking of 'date and 'month ' separately had not occurred to me.

I've also found that if you 'create' a Calendar table and use the dates from that it does not re- calculate,
conversely if you use the dates from the 'sales' in the pivot but the Calendar dates in the measure it does.
I've had a response on Excel forum and uploaded sheet with two pivots one using dates from Calendar and the other dates form 'sales' table and how they both behave. Your measure is 'using Sales Filer Date and Month' .

https://www.excelforum.com/office-365/1382041-dax-running-total-that-does-not-recalculate-at-each-group.html

Richard