Rolling 8 weeks avg for weekly, monthly, quarterly

[EZiamslow]

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!

FYWW	Out	Trash	Yield	Rolling 8 Weeks Avg
FY19 WW01	32	14	70%	70%
FY19 WW36	62	27	70%	70%
FY19 WW37	34	22	61%	67%
FY19 WW38	34	54	39%	60%
FY19 WW39	40	58	41%	56%
FY19 WW40	29	48	38%	53%
FY19 WW41	20	58	26%	49%
FY19 WW42	31	48	39%	48%
FY19 WW43	49	27	64%	47%
FY19 WW44	44	22	67%	47%
FY19 WW45	45	54	45%	45%
FY19 WW46	55	58	49%	46%
FY19 WW47	44	48	48%	47%
FY19 WW48	39	43	48%	48%
FY19 WW49	50	32	61%	53%
FY19 WW50	45	24	65%	56%
FY19 WW51	41	25	62%	56%
FY19 WW52	47	49	49%	53%
FY20 WW01	48	51	48%	54%
FY20 WW02	31	41	43%	53%
FY20 WW03	21	35	38%	52%
FY20 WW04	45	24	65%	54%

[Anonymous · Accepted solution]

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

[EZiamslow]

@Anonymous 

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!

[Anonymous]

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. 

[EZiamslow]

I will try this method. Thanks.