Calculate Velocity based on three Sprints

[Gniady]

Hi Power BI Community,

I am struggling with calculating 3 Month Velocity for each team. It should be based on three sprints (but calculated backwards, not only three last sprints), for example:

B | 159 = Avg(B | 159, B | 158, B | 157)
B | 158 = Avg(B | 158, B | 157, B | 156)
B | 157 = Avg(B | 157, B | 156, B | 155)
and so on for each team.

Example table below, field "Velocity for 3 Sprints" has been manually calculated in Excel it should always be calculated based on sprint - 2 (if there are any, for example B | 155 will velocity will equal 22 as there are no previous sprints):

Sprint	Team	Sprint Number	Completed	Velocity 3 Sprints
A | 7	A	7	10	11.0
A | 8	A	8	11	10.5
A | 9	A	9	12	10.0
B | 155	B	155	22	22.0
B | 156	B	156	23	22.5
B | 157	B	157	24	23.0
B | 158	B	158	25	24.0
B | 159	B	159	66	38.3
C | 61	C	61	33	33.0
C | 62	C	62	44	38.5
C | 63	C	63	22	33.0
C | 64	C	64	33	33.0
C | 65	C	65	23	26.0
C | 66	C	66	11	22.3

Im aiming at presenting the data like in the picture below, but the formula is uncrakable for me.

[Anonymous · Accepted solution]

Hi @Gniady 

Please try the following Measure:

Month Velocity = 
VAR CurrentSprintNumber = SELECTEDVALUE(TeamSprints[Sprint Number])
VAR TeamName = SELECTEDVALUE(TeamSprints[Team])
RETURN
AVERAGEX(
    FILTER(
        ALL(TeamSprints),
        TeamSprints[Team] = TeamName && 
        TeamSprints[Sprint Number] <= CurrentSprintNumber && 
        TeamSprints[Sprint Number] >= CurrentSprintNumber - 2
    ),
    TeamSprints[Completed]
)

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.