Moving average with Measured Index

[MNLegoman]

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!

[parry2k]

@MNLegoman glad it worked out. Cheers!!

[parry2k · Accepted solution]

@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]

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]

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

[MNLegoman]

Hope this works

(Link Removed)