Slicer changing 4 week average rolling calculation

[GGerritsen]

I have a report that calculate the rolling average 4 weeks in sales.

4Wk rolling Avg Sales =

VAR NumOfDays = 4 * 7

VAR LastDateInPeriod =

    MAX ( 'Forecast History'[Start of Week] )

VAR FirstDateInPeriod = LastDateInPeriod - NumOfDays

VAR LastDateWithSales =

    CALCULATE ( MAX ( 'Forecast History'[Start of Week]), REMOVEFILTERS () )

VAR Result =

    CALCULATE (

        SUMX( VALUES ('Forecast History'), 'Forecast History'[Sales] ),

        'Forecast History'[Start of Week] > FirstDateInPeriod,

        'Forecast History'[Start of Week]<= LastDateInPeriod

    )

RETURN

    IF ( LastDateInPeriod <= LastDateWithSales, Result )/4

the results works fine

 I have created a measure that adds in a relative week number, reason for this is that i am putting a filter to always only show the last 12 weeks rolling average (dynamic - so no need for me to change weekly)

Relative Week B =

    VAR vToday = TODAY()

    VAR vWeekEndDate = [Date] - WEEKDAY ( [Date], 1 ) + 7

    VAR vWeekEndDateToday = vToday - WEEKDAY ( [Date], 1 ) + 7

    VAR vResult = CONVERT ( ( vWeekEndDate - vWeekEndDateToday ) / 7, INTEGER )

    RETURN vResult

If i put the relative wek filter for the 12 weeks my average sales calculations changes as well (this should not happen)

I have not been able to figure out where to change the formula to not use the filter in the calculation, but still use the filter on the visual in order to only show the past 12 weeks average.

Gerben

[sjoerdvn]

So, because there is a filter context active for the last 12 weeks filter, the date context must be expanded to also include the four weeks before the last 12 weeks. A well placed ALL() function should achieve that. 
However, I am currently unable to help with the actual code because some things are not clear from the write up,  such as:
Are you using a date dimension, if so how is it related to the 'Forecast History' table. Is the relative week filtering done via a measure filtering the visual or are you using a computed column etc etc.

[v-ssriganesh]

Hi @GGerritsen,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I've reproduced your scenario using sample data and have found a solution that should address the issue with the slicer affecting your 4-week rolling average calculation.

I've attached a sample .pbix file to this reply, which demonstrates the solution. Please download it and take a look.

The key to resolving the problem is to use the following DAX measure:

4Wk rolling Avg Sales (Corrected) =

    VAR CurrentDate = MAX('SalesData'[StartDate])

    VAR StartDateWindow = CurrentDate - 28

    RETURN

        CALCULATE(

            AVERAGE('SalesData'[Sales]),

            FILTER(

                ALLSELECTED('SalesData'),

                'SalesData'[StartDate] > StartDateWindow && 'SalesData'[StartDate] <= CurrentDate

            ),

            REMOVEFILTERS('SalesData'[Year])

        )

• ALLSELECTED('SalesData') this checks that all filters on the SalesData table are ignored, except for those directly applied by the slicer.
• REMOVEFILTERS('SalesData'[Year]) this explicitly removes the filter from the "Year" column, which is the column being used in the slicer.
  
  

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[GGerritsen]

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)

[v-ssriganesh · Accepted solution]

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.

[bhanu_gautam]

@GGerritsen , Try using

DAX
4Wk rolling Avg Sales =
VAR NumOfDays = 4 * 7
VAR LastDateInPeriod = MAX('Forecast History'[Start of Week])
VAR FirstDateInPeriod = LastDateInPeriod - NumOfDays
VAR LastDateWithSales = CALCULATE(MAX('Forecast History'[Start of Week]), REMOVEFILTERS())
VAR Result =
CALCULATE(
SUMX(VALUES('Forecast History'), 'Forecast History'[Sales]),
'Forecast History'[Start of Week] > FirstDateInPeriod,
'Forecast History'[Start of Week] <= LastDateInPeriod,
REMOVEFILTERS('Forecast History'[Relative Week B])
)
RETURN
IF(LastDateInPeriod <= LastDateWithSales, Result / 4)

[GGerritsen]

I'm getting the same result (slicer still meesing with the numbers)  no change from original formula / result