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
Highlighted
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.

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.

Highlighted
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!

Announcements

Back to School Contest

Engage and empower students with Power BI!

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 342 members 3,418 guests
Recent signins: