cancel
Showing results for
Did you mean:
Frequent Visitor

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

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

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

hi, @DFisherZ

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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

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!

6 REPLIES 6
Super User

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

Hi,

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

Highlighted
Frequent Visitor

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

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.

Community Support Team

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

hi, @DFisherZ

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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

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.

Community Support Team

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

hi, @DFisherZ

Here is my demo pbix file, please try it.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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

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!