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 ...
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.⚡

parry2k's avatar
parry2k
Super 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)])