topic Calculate average velocity from previous 3 sprints in DAX Commands and Tips

Calculate average velocity from previous 3 sprints

Anonymous — Thu, 01 Feb 2024 19:49:30 GMT

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!

Re: Calculate average velocity from previous 3 sprints

Greg_Deckler — Thu, 01 Feb 2024 20:01:26 GMT

Anonymous Try this: Better Rolling Average - Microsoft Fabric Community

Re: Calculate average velocity from previous 3 sprints

Anonymous — Thu, 01 Feb 2024 23:47:22 GMT

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.

Re: Calculate average velocity from previous 3 sprints

Greg_Deckler — Fri, 02 Feb 2024 15:48:16 GMT

Anonymous 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])