Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |