cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EZiamslow Regular Visitor
Regular Visitor

Rolling 8 weeks avg for weekly, monthly, quarterly

Hi,

I have been searching for the past two days and I'm still stuck. I'm hoping some of you could help me. I have FYWW(Weekly) and FYQ(Quarterly) columns to show exactly how I want on the charts (not sure if this is the way to do that). You can see the example for FYWW below. I converted regular dates to Fiscal years with Fiscal work week. I have separate charts with buttons for weekly, monthly and quarterly. The end-users click the button and show the reports that they want to see.

 

So, my question is how do I get rolling 8weeks avg from this? What would be the best way to tackle this problem? I will appreciate any input. Thanks!

 

FYWWOutTrashYieldRolling 8 Weeks Avg
FY19 WW01321470%70%
FY19 WW36622770%70%
FY19 WW37342261%67%
FY19 WW38345439%60%
FY19 WW39405841%56%
FY19 WW40294838%53%
FY19 WW41205826%49%
FY19 WW42314839%48%
FY19 WW43492764%47%
FY19 WW44442267%47%
FY19 WW45455445%45%
FY19 WW46555849%46%
FY19 WW47444848%47%
FY19 WW48394348%48%
FY19 WW49503261%53%
FY19 WW50452465%56%
FY19 WW51412562%56%
FY19 WW52474949%53%
FY20 WW01485148%54%
FY20 WW02314143%53%
FY20 WW03213538%52%
FY20 WW04452465%54%
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Rolling 8 weeks avg for weekly, monthly, quarterly

The best (well in my view anyway) is to get yourself a dedicated date table with the columns you need ( such as fiscal year and such) on of the columns should be an index so you know where you "are" in the current filter context and how where to go back to.  Since you are using fiscal years you cannot use the built in time intelligence. Generic code would look something like:

8 week average=
CALCULATE(
    [AVERAGE MEASURE]
    FILTER(
        ALL( DATE TABLE),
        Date Table[WeekID] <= MAX( Date Table[WeekID])
        &&
        DATE TABLE[WeekID] >= MAX( Date Table[WeekID]) -7 
    )
)

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: Rolling 8 weeks avg for weekly, monthly, quarterly

The best (well in my view anyway) is to get yourself a dedicated date table with the columns you need ( such as fiscal year and such) on of the columns should be an index so you know where you "are" in the current filter context and how where to go back to.  Since you are using fiscal years you cannot use the built in time intelligence. Generic code would look something like:

8 week average=
CALCULATE(
    [AVERAGE MEASURE]
    FILTER(
        ALL( DATE TABLE),
        Date Table[WeekID] <= MAX( Date Table[WeekID])
        &&
        DATE TABLE[WeekID] >= MAX( Date Table[WeekID]) -7 
    )
)

View solution in original post

EZiamslow Regular Visitor
Regular Visitor

Re: Rolling 8 weeks avg for weekly, monthly, quarterly

I will try this method. Thanks. 

EZiamslow Regular Visitor
Regular Visitor

Re: Rolling 8 weeks avg for weekly, monthly, quarterly

@Nick_M 

Thank you for pointing me to the right direction. However, your code works as long as the WeekID doesn't go back to 1 or last few weeks where the data is ending. I'm using fiscal year so at the beginning of July, WeekID goes back to 1. All my Week 1 and up are wrong. How can I fix it?

 

Updates: never mind I figured it. I used Dates instead of Weeks. It's working now. Thanks!

Super User
Super User

Re: Rolling 8 weeks avg for weekly, monthly, quarterly

cool, glad you got it to work. You want a running count of weeks going back, not one that restarts. This can be done pretty easily in Power Query or a calculated column in your date table, but looks like you are all set. 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)