Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not 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

    • Anonymous's avatar
      Anonymous
      Not 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:

      NameIDYYYYWWWeekIndexWeekWorkHours13wmaWorkHours

      Ferk

      45632023176941.7510.43423
      Ferk45632023187041.530.23423
      Ferk45632023197141.2529.23523
      Ferk45632023207248.7523.02042
      Ferk45632023217332.523.10342
      Ferk4563202322744824.20402
      Ferk45632023237541.2538.20304
      Ferk45632023247632.2535.29482
      Ferk45632023267852.2539.39203
      Ferk45632023277933.7536.30403
      Ferk45632023288033.2530.32032
      Ferk45632023298148.534.20403
      Ferk45632023308232.2535.93042
      Ferk45632023318342.7540.24343
      Ferk45632023328440.540.23532
      Ferk45632023338548.538.43092
      Ferk45632023348632.2538.29492
      Ferk4563202335875136.30451
      Ferk45632023368841.539.38583
      Ferk4563202337894238.57473
      Ferk4563202338904037.58375
      Ferk45632023399136.533.38583
      Ferk45632023409240.2532.45059


      This is the goal:



      Any help/tip is welcome. If you need more information/clarification tell me.

      Thank you,
      Ferk