Calculate Dynamic Rolling 4 Week Average based on multi-level category filters and using only week #

[Anonymous]

Hi All,

I'm not sure where to start with this issue. I would like to add a dynamic rolling 4 week column that changes based on the data filters.

Here is an example dataset:

Fiscal Week Number	Actual	Expected	Division	Sites	KPI	SKU
1	1614	1038	A	Zone1	A	A
2	4581	1012	A	Zone1	A	B
3	3094	1347	A	Zone1	A	C
4	4970	1072	A	Zone1	A	D
5	1063	1562	A	Zone1	A	E
1	4383	1177	A	Zone1	B	A
2	2277	1021	A	Zone1	B	B
3	619	1519	A	Zone1	B	C
4	1707	1245	A	Zone1	B	D
5	2875	1613	A	Zone1	B	E
1	4290	1322	A	Zone2	C	A
2	3580	1163	A	Zone2	C	B
3	4998	1771	A	Zone2	C	C
4	2065	1033	A	Zone2	C	D
5	1701	1881	A	Zone2	C	E

Here are the desired results:

When I don't use any filters: (Rolling 4 week average is calculated by taking week 4 data and go back 4 weeks and average the values so (8742+8711+10438+10287)/4 = 9544.5)

Fiscal Week Number	Sum of Actual	Rolling 4 Week Avg
1	10287
2	10438
3	8711
4	8742	9544.5
5	5639	8382.5

When I use one filter (Sites = Zone1)

Fiscal Week Number	Sum of Actual	Rolling 4 Weeks
1	5997
2	6858
3	3713
4	6677	5811.25
5	3938	5296.5

When I use two fitlers (Sites = Zone1 and KPI = A)

Fiscal Week Number	Sum of Actual	Rolling 4 Weeks
1	1614
2	4581
3	3094
4	4970	3564.75
5	1063	3427

I've added a sample .pbix file and the relevant excel for this issue.

Thanks for the help!

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could use this formula to create a measure:

Rolling 4 Weeks = 
VAR _table =
    CALCULATETABLE (
        VALUES ( 'data'[Fiscal Week Number] ),
        FILTER (
            ALLSELECTED ( 'data'[Fiscal Week Number] ),
            'data'[Fiscal Week Number]
                >= MAX ( 'data'[Fiscal Week Number] ) - 3
                && 'data'[Fiscal Week Number] <= MAX ( 'data'[Fiscal Week Number] )
        )
    )
RETURN
    IF(COUNTAX(_table,[Fiscal Week Number])=4,AVERAGEX ( _table, CALCULATE ( SUM ( 'data'[Actual] ) ) ))

Result:

Best Regards,

Lin

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could use this formula to create a measure:

Rolling 4 Weeks = 
VAR _table =
    CALCULATETABLE (
        VALUES ( 'data'[Fiscal Week Number] ),
        FILTER (
            ALLSELECTED ( 'data'[Fiscal Week Number] ),
            'data'[Fiscal Week Number]
                >= MAX ( 'data'[Fiscal Week Number] ) - 3
                && 'data'[Fiscal Week Number] <= MAX ( 'data'[Fiscal Week Number] )
        )
    )
RETURN
    IF(COUNTAX(_table,[Fiscal Week Number])=4,AVERAGEX ( _table, CALCULATE ( SUM ( 'data'[Actual] ) ) ))

Result:

Best Regards,

Lin

[Anonymous]

HI @v-lili6-msft  thank you for looking to tackle the problem. Can you attach your .pbix file with the solution? I tried using the measure you prescribed, but I wasn't able to replicate your solution that you showed in your screen shots.

[v-lili6-msft]

hi, @Anonymous 

Here is my demo pbix file, please try it.

Best Regards,

Lin

[Anonymous · Accepted solution]

Hello @v-lili6-msft ,

Thanks for the file. I think I know the issue. I think as soon as SKU gets introduced into the big table with everything. You won't see a Rolling 4 Weeks because there are never more than 4 weeks consecutively for SKUs.

I'll test this formula out with my real file and see if it translates over. Thanks for following up. I'll mark done as soon as I test it with my actual work file.

Thanks!

[Ashish_Mathur]

Hi,

Do you have a Date column in your dataset?  If yes, then the problem becomes fairly easy to solve.

[Anonymous]

Hello there is no date column. I agree if there was a date column it would be easier to solve, but I'm only work with week numbers. I tried joining to a date table, but using the built in rolling average measure doesn't help solve the issue that comes with the multi leveled filters.