Forum Discussion
13 Week Moving Average
Hi,
My data looks like this (ignore the values):
I have a Matrix table with Name in Rows, YYYYWW in Columns, WeekWorkHours and 13wmaWorkHours in Values.
For the 13wmaWorkHours (calculated column) i'm using this formula:
13wmaWorkHours =
var selectedweekindex = Table[WeekIndex]
var selectedperson = Table[ID]
return
SUMX(
TOPN(
13,
FILTER(
Table
Table[WeekIndex] <= selectedweekindex &&
Table[WeekIndex] > selectedweekindex -13 &&
Table[SAP ID] = selectedperson
),
Table[WeekIndex],DESC
),
Table[WeekWorkHours]
)/13
I need to do this update: only start counting and filling the column 13wmaWorkHours from the 13th week based on the last 12 weeks. So for the first 12th weeks the column 13wmaWorkHours should be empty and then on the 13th week do the moving average based on WeekWorkHours of the last 12 weeks. I think I also need to find a way to sort the YYYYWW for each employee because the value of 13wmaWorkHours for the first 12 weeks will be empty.
So the matrix table will look like this:
Only from the 13week of employee Ferk the 13wmaWorkHours value wil appear.
Any help/tip is welcome. If you need more information/clarification tell me.
Thank you,
Ferk
2 Replies
- AnonymousNot applicable
Hi Anonymous ,
Could you please provide some raw data in your tables (exclude sensitive data) with Text format, the calculation logic and your expected result base on the provided sample data? It would be helpful to find out the solution. You can refer the following link to share the required info:
How to provide sample data in the Power BI Forum
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
- AnonymousNot applicable
Hey Anonymous ,
Thank you for your answer and sorry for not answering. I still have the same problem.
Here you have the raw data:Name ID YYYYWW WeekIndex WeekWorkHours 13wmaWorkHours Ferk
4563 202317 69 41.75 10.43423 Ferk 4563 202318 70 41.5 30.23423 Ferk 4563 202319 71 41.25 29.23523 Ferk 4563 202320 72 48.75 23.02042 Ferk 4563 202321 73 32.5 23.10342 Ferk 4563 202322 74 48 24.20402 Ferk 4563 202323 75 41.25 38.20304 Ferk 4563 202324 76 32.25 35.29482 Ferk 4563 202326 78 52.25 39.39203 Ferk 4563 202327 79 33.75 36.30403 Ferk 4563 202328 80 33.25 30.32032 Ferk 4563 202329 81 48.5 34.20403 Ferk 4563 202330 82 32.25 35.93042 Ferk 4563 202331 83 42.75 40.24343 Ferk 4563 202332 84 40.5 40.23532 Ferk 4563 202333 85 48.5 38.43092 Ferk 4563 202334 86 32.25 38.29492 Ferk 4563 202335 87 51 36.30451 Ferk 4563 202336 88 41.5 39.38583 Ferk 4563 202337 89 42 38.57473 Ferk 4563 202338 90 40 37.58375 Ferk 4563 202339 91 36.5 33.38583 Ferk 4563 202340 92 40.25 32.45059
This is the goal:
Any help/tip is welcome. If you need more information/clarification tell me.
Thank you,
Ferk