Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
User | Count |
---|---|
90 | |
73 | |
67 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |