Calculate average velocity from previous 3 sprints

[Benx]

Hello all,

I'm trying to calculate the average velocity from the previous 3 sprints, so I can then calculate the variance of the current sprint velocity against the average velocity from the prior three sprints.

Here's a trimmed down view of the data I'm working with:

Sprint name	Complete date	Sprint Velocity
ENRL | FY24 Q2 SP09	11/29/2023	39
ADM | FY24 Q2 SP09	11/29/2023	17
AAAMT | FY24 Q2 SP09	11/29/2023	34
ENRL | FY24 Q2 SP10	12/13/2023	46
ADM | FY24 Q2 SP10	12/13/2023	8
AAAMT | FY24 Q2 SP10	12/13/2023	13
ENRL | FY24 Q2 SP11	12/27/2023	16
ADM | FY24 Q2 SP11	12/27/2023	19
AAAMT | FY24 Q2 SP11	12/27/2023	26
ADM | FY24 Q2 SP12	1/17/2024	27
ENRL | FY24 Q2 SP12	1/17/2024	13
AAAMT | FY24 Q2 SP12	1/17/2024	0

Here's the approach I've tried, but it's not producing the expected result:

Avg Velocity Past 3 Sprints =

CALCULATE(

    AVERAGE('Sprint Predictability Data'[Completed work]),

    FILTER(

        ALL('Sprint Predictability Data'),

        'Sprint Predictability Data'[Complete date] < MAX('Sprint Predictability Data'[Complete date]) &&

        'Sprint Predictability Data'[Complete date] >= MAX('Sprint Predictability Data'[Complete date]) -3

    )

)

Can any of you offer suggestions on how I might write this Dax measure?

Thank you!

[Greg_Deckler]

@Benx Try this: Better Rolling Average - Microsoft Fabric Community

[Benx]

Thank you @Greg_Deckler ! Unfortunately, I'm having trouble making your solution work, as I'm not always dealing with a stadard period for the rolling avg. I want to dynamically capture the Sprint Velocity from the  prior three recorded dates. These may fall over the past 6 weeks, but not always. 

I created a DateIndex to assign a number to each distinct date, thinking this would give me what I needed to calcualte the rolling average, without using time intelligence functions. This also has not worked.
This is an example of what I'm trying to acheive.

[Greg_Deckler · Accepted solution]

@Benx So like this? PBIX is attached.

Better Rolling Average =
VAR __EndDate = MAX('Table'[DateIndex])
VAR __StartDate = __EndDate - 2
VAR __Table =
SUMMARIZE(
FILTER(ALL('Table'),[DateIndex]>=__StartDate && [DateIndex]<=__EndDate),
'Table'[DateIndex],
"__Value",SUM('Table'[Sprint Velocity])
)
RETURN
AVERAGEX(__Table,[__Value])