Forum Discussion

purpletiger's avatar
purpletiger
Frequent Visitor
7 years ago
Solved

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 ha...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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's avatar
purpletiger
Frequent Visitor
7 years ago

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's avatar
v-danhe-msft
Microsoft Employee
7 years ago

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