Help Needed - Rolling 12 Month Sales ignoring Year and Week Slicer

[Anonymous]

Hello Forum helpers

Help needed in creating a Rolling 12 Month Sales ignoring Year and Week slicer. I've created a rolling 12 month for sales, but problem lies when using the slicer (Lets say I enter 2020), the Rolling sale calculation throwing wrong figure. Measures included in my PBIX file with Date table [Cumulative Rolling Sales, Month Running Index].

What I'm aiming for the 12 month Rolling measure which ignore Year and Week. Added a PBIX File to download with my example below.

https://veetee0-my.sharepoint.com/:u:/g/personal/mnair_veetee_com/ET3qSWrPbG9GlpLfElsnuDcBQzKtd-TLXZ...

Measure created based on solution provided by [TomMartens] https://community.powerbi.com/t5/Desktop/Rolling-Avg-calculation-should-ignore-date-slicer/td-p/6891....

Appreciate your help in advance.

Many Thanks

[technolog · Accepted solution]

To achieve this, you'll want to use the ALL function in DAX to remove any filters applied by the slicers. The ALL function essentially clears any filters that might be applied to the specified table or column.

Given that you already have a measure for Cumulative Rolling Sales and a Month Running Index, let's try to modify your Rolling 12 Month Sales measure to ignore the slicers:

Rolling 12 Month Sales =
CALCULATE(
[Cumulative Rolling Sales],
FILTER(
ALL('DateTable'),
'DateTable'[Month Running Index] <= MAX('DateTable'[Month Running Index]) &&
'DateTable'[Month Running Index] > MAX('DateTable'[Month Running Index]) - 12
)
)
Here's a breakdown of what's happening:

CALCULATE changes the context in which the data is evaluated.
FILTER is used to create a new table that contains only the rows you're interested in.
ALL('DateTable') removes any filters on the DateTable, ensuring that the slicers don't affect the calculation.
The conditions inside the FILTER function ensure that only the last 12 months, including the current month, are considered.
By using this measure, even if you select a specific year or week using the slicer, the Rolling 12 Month Sales measure should give you the total sales for the last 12 months, ignoring the slicer selection.