Forum Discussion

Stijn06's avatar
Stijn06
New Member
3 years ago
Solved

Accumulated Total with filtering

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
Need Help
  • Adescrit's avatar
    Adescrit
    3 years ago

    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] )))


1 Reply

  • Adescrit's avatar
    Adescrit
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    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] )))