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.

View solution in original post

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!

View solution in original post

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.

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.

View solution in original post

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 959 guests
Please welcome our newest community members: