Forum Discussion

MNLegoman's avatar
MNLegoman
Frequent Visitor
5 years ago
Solved

Moving average with Measured Index

Hello Community, 

   I'm brand new to Power BI, so please bear with me. I'm converting my excel dashboard into a power bi dashboard. One of my charts is a 4-week moving average. I'm able to do this in excel with a pivot table and a separate column that calculates the average of the previous 4 weeks (charted as the red line).

 

 

Ok, so now I'm converting to Power bi. I've got my "matrix" view of the table and added a "row number" column (created as a measure because it is relative to the filters), but I cannot for the life of me figure out how to calculate the 4 week moving average. All of the examples I run into have the "index" as a column not a measure OR they are averaging a column and not a measure. In my case, Row_Number AND Avg_Task_Dur are measures impacted by the filters.

 

Avg_Task_Dur = AVERAGE(CFTaskDetails[Task Dur (total)])
 
Row_Number =
CALCULATE (
DISTINCTCOUNT(CFTaskDetails[Notif Closd YR-WK]),
FILTER ( ALLSELECTED (CFTaskDetails[Notif Closd YR-WK] ), CFTaskDetails[Notif Closd YR-WK] <= MAX( CFTaskDetails[Notif Closd YR-WK]) ))
 

 

 

Note: the 4 week moving average is an average of the averages for the last 4 weeks.

 

I would greatly appreciate any help... Thanks!

 

 

Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    MNLegoman here is the measure for you:

     

    Avg_Task_Dur = 
VAR __currentRowNumber = [Row_Number]
VAR __startRow = [Row_Number]  - 3
VAR __Result = 
    AVERAGEX ( 
        SUMMARIZE ( 
            ALLSELECTED ( CFTaskDetails[Notif Closd YR-WK] ), 
            CFTaskDetails[Notif Closd YR-WK], 
            "@Row", [Row_Number] 
        ),
        IF ( [@Row] >= __startRow && [@Row] <= __currentRowNumber,
            CALCULATE ( 
                SUM ( CFTaskDetails[Task Dur (total)] )
            )
        )
    )
RETURN __Result

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    MNLegoman here is the measure for you:

     

    Avg_Task_Dur = 
VAR __currentRowNumber = [Row_Number]
VAR __startRow = [Row_Number]  - 3
VAR __Result = 
    AVERAGEX ( 
        SUMMARIZE ( 
            ALLSELECTED ( CFTaskDetails[Notif Closd YR-WK] ), 
            CFTaskDetails[Notif Closd YR-WK], 
            "@Row", [Row_Number] 
        ),
        IF ( [@Row] >= __startRow && [@Row] <= __currentRowNumber,
            CALCULATE ( 
                SUM ( CFTaskDetails[Task Dur (total)] )
            )
        )
    )
RETURN __Result

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • MNLegoman's avatar
      MNLegoman
      Frequent Visitor
      5 years ago

      OMG, you are a lifesaver! With one small tweak, (change SUM to AVERAGE) this is exactly what I needed. 

      Thank you, thank you!

       

      Change made:

      average(CFTaskDetails[Task Dur (total)])
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    MNLegoman It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.