Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ferk_
Frequent Visitor

13 Week Moving Average

Hi,

My data looks like this (ignore the values):
Screenshot (100).png

I have a Matrix table with Name in Rows, YYYYWW in Columns, WeekWorkHours and 13wmaWorkHours in Values.
Screenshot (101).png

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:
Screenshot (102).png

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 2
v-yiruan-msft
Community Support
Community Support

Hi @ferk_ ,

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hey @v-yiruan-msft ,

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:

Screenshot (102).png

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

Thank you,
Ferk

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors