Average Velocity for last 3 Sprints

[Anonymous]

I need to calculate the average velocity over the last 3 sprints and show it for each Area and Team. I'd like to display it on a visual over Sprints (periods of time typically 2-3 weeks).
I have a fact WorkItems table, where changes made to every work item is recorded:

WorkItemId	ChangedDate	State	IterationId	AreaID	SPDelivered
1000012	10/10/2022 08:46	New	It2	Ar7
1000012	10/10/2022 11:54	New	It2	Ar7
1000012	13/10/2022 08:30	Closed	It2	Ar7	2
1000013	10/10/2022 08:46	New	It2	Ar7
1000013	10/10/2022 11:54	New	It2	Ar7
1000013	13/10/2022 08:31	Closed	It2	Ar7	2
1000014	10/10/2022 08:46	New	It2	Ar7
1000014	10/10/2022 11:54	New	It2	Ar7
1000014	14/10/2022 08:31	Closed	It2	Ar7	2

I have a dim Areas table with Areas:

AreaId	AreaName
Ar1	AN a1
Ar10	AN b1
Ar100	AN c1

I have 'dim Iterations' table with Sprints and their start and end dates:

IterationId	StartDate	EndDate	SprintNumber
It325	05/01/2022 00:00	18/01/2022 23:59	1
It184	10/01/2022 00:00	30/03/2022 23:59	2
It303	14/02/2022 00:00	11/03/2022 23:59	3
It218	18/02/2022 00:00	12/04/2022 23:59	4

And finally there is the 'AreasToTeams' table which shows what teams are responsible for what Areas. A Team can have multiple Areas and an Area can have multiple Teams:

TeamName	AreaId
Team A1	Ar189
Team A1	Ar217
Team A2	Ar31
Team A3	Ar90
Team A4	Ar90

The Data Model Looks Like this:

Let's say Team A1 has delivered 10 points in Sprint 1, then 4 points in Sprint 2, then 13 points in Sprint 3. Their Velocity in Sprint 4 would be (10+4+13) / 3 = 9. To check what Sprint has an Item been delivered I compare the IterationId in 'fact Workitems' with 'dim Iterations' SprintNumber.
I tried to merge all the tables into one 'fact WorkItems', but the refreshing will often time out and the visual also won't load.  Besides the method wouldn't work if I wanted to display Velocity for each team.

Velocity = 
VAR b = MAX( 'fact WorkItems'[SprintNumber]) - 1
VAR a =  b - 2
RETURN
 CALCULATE ( SUM('fact WorkItems'[SPDelivered])/3 ,
 FILTER (
 ALLEXCEPT ( 'fact WorkItems','fact WorkItems'[AreaName] ),
 'fact WorkItems'[SprintNumber] >= a
 && 'fact WorkItems'[SprintNumber] <= b
 )
 )

Please help.
Sample files attached are here

[Anonymous]

Hi @Anonymous ,

I updated your sample pbix file(see the attachment), please check if that is what you want. Please update the formula of measure [Velocity] as below:

Velocity = 
VAR _selteam =
    SELECTEDVALUE ( 'AreasToTeams'[TeamName] )
VAR _areas =
    CALCULATETABLE (
        VALUES ( 'AreasToTeams'[AreaId] ),
        FILTER ( 'AreasToTeams', 'AreasToTeams'[TeamName] = _selteam )
    )
VAR _iterids =
    CALCULATETABLE (
        VALUES ( 'fact workitems'[IterationId] ),
        FILTER ( 'fact workitems', 'fact workitems'[AreaId] IN _areas )
    )
VAR _countofsprint =
    CALCULATE (
        DISTINCTCOUNT ( 'dim iterations'[SprintNumber] ),
        FILTER ( 'dim iterations', 'dim iterations'[IterationId] IN _iterids )
    )
VAR _points =
    CALCULATE (
        SUM ( 'fact WorkItems'[SPDelivered] ),
        FILTER ( 'fact workitems', 'fact workitems'[AreaId] IN _areas )
    )
RETURN
    IF ( ISBLANK ( _points ), BLANK (), DIVIDE ( _points, _countofsprint ) )

Best Regards

[Anonymous]

Hi, thanks very much for that. This isn't quite what I'm trying to achieve.
I want to have a column visual plotted over SprintNumber (x Axis), where each column shows Velocity for that srpint. Once again Velocity is a sum of all points delivered in each of the last three sprints divided by 3.
From a Team's selection,  this would look like this: Team A has delivered (in all of their areas) 20 points in Sprint 1, 10 in Sprint 2 and 0 in Sprint 3. Their Velocity in Sprint 4 is (20+10+0)/3=10
From an Area Selection: In Area A1 there has been delivered 5 points in Sprint 1, 5 points in Sprint 2 and 2 points in Sprint 3. That Area's Velocity in Sprint 4 is (5+5+2)/3=4
It's complicated, because I want to be able to view this from any perspective (Team and Area) and one Team can have 1 or more Areas. Equally 1 Area can 1 or more Teams.
I've noticed the sample I attached wasn't the best, so I've changed it for a better one I think.

[daXtreme]

You can't attach files. But you can give a link to the file on a shared drive. Don't forget to grant public access.

[Anonymous]

Attached, thanks for the tip!