Accumulated Total with filtering

[Stijn06]

Hello,

I've made a accumulated sum per month / year:

Accumulated = CALCULATE (SUM ( 'Turnover Monthly'[Gross Invoiced] ),FILTER (ALL ( 'Turnover Monthly' ),'Turnover Monthly'[Date Invoice Day Month Year] <= MAX ( 'Turnover Monthly'[Date Invoice Day Month Year] )&& 'Turnover Monthly'[Date Invoice Year] = MAX ( 'Turnover Monthly'[Date Invoice Year] )))

But now when I filter on another slicer (for instance 'Sales District'), the accumulated sum (measure) per month doesn't change to that district but keeps giving the total.

What goes wrong ?

Thanks in advance

Stijn

[Adescrit · Accepted solution]

Hi @Stijn06 

The issue is being caused by the ALL function in your DAX. This function is telling the CALCULATE function to remove / ignore all filters applied to the 'Turnover Monthly' table, regardless of whether those filters are applied within the DAX statement itself or via slicers in the report.

To overcome the issue you should create a Calendar dimension table, if you haven't already. Connect this calendar dimension table to your 'Turnover Monthly' table and apply the filters in your CALCULATE function (including the ALL function) to the dimension table instead of the Turnover Monthly table.

So your DAX would look something like this:

Accumulated = CALCULATE (SUM ( 'Turnover Monthly'[Gross Invoiced] ),FILTER (ALL ( 'Calendar' ),'Calendar'[Date Invoice Day Month Year] <= MAX ( 'Calendar'[Date Invoice Day Month Year] )&& 'Calendar'[Date Invoice Year] = MAX ( 'Calendar'[Date Invoice Year] )))