Forum Discussion
4 Week Average delayed by one week
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
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
3 Replies
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
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.
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
