cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DFisherZ Frequent Visitor
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 NumberActualExpectedDivisionSitesKPISKU
116141038AZone1AA
245811012AZone1AB
330941347AZone1AC
449701072AZone1AD
510631562AZone1AE
143831177AZone1BA
222771021AZone1BB
36191519AZone1BC
417071245AZone1BD
528751613AZone1BE
142901322AZone2CA
235801163AZone2CB
349981771AZone2CC
420651033AZone2CD
517011881AZone2CE



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 NumberSum of ActualRolling 4 Week Avg
110287 
210438 
38711 
487429544.5
556398382.5

 

When I use one filter (Sites = Zone1)

Fiscal Week NumberSum of ActualRolling 4 Weeks
15997 
26858 
33713 
466775811.25
539385296.5

 

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

Fiscal Week NumberSum of ActualRolling 4 Weeks
11614 
24581 
33094 
449703564.75
510633427

 

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
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:

5.JPG6.JPG7.JPG

 

 

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.
DFisherZ Frequent Visitor
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
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
DFisherZ Frequent Visitor
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
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:

5.JPG6.JPG7.JPG

 

 

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.
DFisherZ Frequent Visitor
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.tested3.pngtested2.pngtested1.png

 

Community Support Team
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.
DFisherZ Frequent Visitor
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!