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

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!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 291 members 3,038 guests
Please welcome our newest community members: