Rolling Average of a Measure

Question

I'm trying to find a 30-day rolling average of a measure [STORY COMPLETED]

The measure is calculated as:

STORY COMPLETED = CALCULATE(COUNT(SprintReportIssues[SPRINT_REPORT_STATUS]), SprintReportIssues[SPRINT_REPORT_STATUS] = "COMPLETED")

SPRINT_REPORT_STATUS can be one of four values: "COMPLETED", "COMPLETED OUTSIDE", "INCOMPLETED" or "REMOVED"

If you look at the image, I want something like:

• Rolling Average Throughput of top row to be 19+18+13/3
• Rolling Average Throughput of second row to be 18+13+14/3
• Rolling Average Throughput of second row to be 13+14+9/3 and so on

enter image description here

Note: all data is on the SprintReportIssues table

I expect a DAX measure for Power BI, and I've tried using AVERAGEX, but I don't seem to be getting it right

Answer 1

This measure will solve your problem:

Rolling Average Throughput = 
VAR __LAST_DATE = 
    LASTDATE('Table'[START DATE])
RETURN
    AVERAGEX(
        DATESBETWEEN(
            'Table'[START DATE],
            DATEADD(__LAST_DATE, -28, DAY),
            __LAST_DATE
        ),
        [STORY COMPLETED]
    )