4 Week Average delayed by one week

[purpletiger]

Hello! I am trying to create a four week rolling average delayed by one week as the current week will be incomplete and skew the results. 

I have created a week number within the Date Table. I have tried the following formula which 

TechHrs 4Wk Avg = CALCULATE(SUM([Tech Hrs]), DATESBETWEEN('DATE'[Week],
Earlier(related(Date[week]))-5, Earlier(related(Date[week]))-1))/4

which does not recognize the Date[Week] Column. 

I am looking for this as a result

With the original data of (truncated)

Thank you in advance. Your help is greatly appreciated! 

Lynne

[v-danhe-msft · Accepted solution]

Hi @purpletiger ,

Based on my test, you could refer to below steps:

Sample data:
(Week is a calculated column:

Week = WEEKNUM('Table1'[Date],2)

Create below measure:

Measure 2 = var week = DISTINCTCOUNT(Table1[Week])
return
IF(week=1,BLANK(),CALCULATE(SUM(Table1[Hrs]),FILTER(ALL('Table1'),'Table1'[Tech]=MAX('Table1'[Tech])&&'Table1'[Week]<=4))/4)

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[purpletiger]

Thank you Daniel - I am actually working on the average based upon the "Week Starting ***). What you have written is helpful and will update this once I get it working. 

[v-danhe-msft]

Hi @purpletiger ,

OK, if your problem could be solved, don't forget to mark one reply as answered to close this topic.

Regards,

Daniel He