Forum Discussion
Slicer changing 4 week average rolling calculation
Hi GGerritsen,
Thank you for update. Based on your explanation, it looks like applying the last 12 weeks filter is also limiting the data available for calculations, which is why the first few weeks in your filtered range don't have the correct rolling average values. Since a 4-week rolling average requires past data to compute correctly, filtering out earlier weeks impacts the calculation.
Instead of applying the last 12 weeks filter to the dataset, we need to adjust the DAX measure so that it still considers all available data but only displays the last 12 weeks.
Try updating your DAX measure like this:
4Wk Rolling Avg Sales Fixed = VAR RollingAvg = AVERAGEX( DATESINPERIOD( 'Calendar'[Date], MAX('Calendar'[Date]), -4, WEEK ), CALCULATE(SUM('Forecast History'[Sales]), ALL('Calendar'[Relative Week])) ) RETURN RollingAvg- Instead of filtering the dataset directly, apply the "Last 12 Weeks" filter only to the visual in the Filters pane. Set it to show values greater than or equal to -11 and less than or equal to 0.
If you find this information useful, please accept it as a solution and give it a 'Kudos' to assist others in locating it easily.
Thank you.
Thank you but I'm getting the same result over and over. I am not using a year filter, I do have a filter only to disply the past 12 weeks, but this filter seems to also limit the calculations to the past 12 weeks. I tried the remove filters and added the filter colum for the past 12 weeks however same result for this as well.
all calculations work, but as soon as i add any filter to display only the last 12 weeks all calcaltions are changing (in this case for the first 4 weeks untill it can calculate the proper average)
Hi GGerritsen,
Thank you for update. Based on your explanation, it looks like applying the last 12 weeks filter is also limiting the data available for calculations, which is why the first few weeks in your filtered range don't have the correct rolling average values. Since a 4-week rolling average requires past data to compute correctly, filtering out earlier weeks impacts the calculation.
Instead of applying the last 12 weeks filter to the dataset, we need to adjust the DAX measure so that it still considers all available data but only displays the last 12 weeks.
Try updating your DAX measure like this:
4Wk Rolling Avg Sales Fixed =
VAR RollingAvg =
AVERAGEX(
DATESINPERIOD(
'Calendar'[Date],
MAX('Calendar'[Date]),
-4,
WEEK
),
CALCULATE(SUM('Forecast History'[Sales]), ALL('Calendar'[Relative Week]))
)
RETURN
RollingAvg- Instead of filtering the dataset directly, apply the "Last 12 Weeks" filter only to the visual in the Filters pane. Set it to show values greater than or equal to -11 and less than or equal to 0.
If you find this information useful, please accept it as a solution and give it a 'Kudos' to assist others in locating it easily.
Thank you.