13 Week Moving Average

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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