calculate 3-sprint rolling average for throughput

[Anonymous]

Hi there!

May I know how I can get a 3-sprint rolling average of througput from table structure below?

Throughput = sum of user story and bug per sprint

Example:

Sprint 10 = 3TP (2US + 1B)

Sprint 11 = 1TP (1US)

SPrint 12 = 2TP (1US + 1B)

Sprint 13 = 2TP (1US + 1B); therefore, for sprint 13, rolling average is 2 = ((Sprint10+11+12 TPs)/3)

Sprint	Work Item Type
Sprint 10	User Story
Sprint 10	Bug
Sprint 10	User Story
Sprint 11	User Story
Sprint 12	User Story
Sprint 12	Bug
Sprint 13	User Story
Sprint 13	Bug

I created a new table with query:

WorkItems =
Var RawWorkItems = GROUPBY('MyWorkItems','MyWorkItems'[Iteration Path], 'MyWorkItems'[Work Item Type], "Total", COUNTX(CURRENTGROUP(), 1))
Return
RawWorkItems

Iteration Path	Work Item Type	Total
Sprint 10	User Story	2
Sprint 10	Bug	1
Sprint 11	User Story	1
Sprint 11	Bug	0
Sprint 12	User Story	1
Sprint 12	Bug	1
Sprint 13	User Story	1
Sprint 13	Bug	1

However, I want to add 3-sprint rolling average row for each sprint like below so I can properly plot them in my chart:

Sprint	Data	Total
Sprint 10	User Story	2
Sprint 10	Bug	1
Sprint 10	Rolling Ave.	3=(sprint9(UserStoryandBugs)+sprint8+sprint7)/3
Sprint 11	User Story	1
Sprint 11	Bug	0
Sprint 12	User Story	1
Sprint 12	Bug	1
Sprint 13	User Story	1
Sprint 13	Bug	1
Sprint 13	Rolling Ave.	2 = (sprint12+sprint11+sprint10)/3

[daxer-almighty · Accepted solution]

First of all, you have to have a column that numbers your sprints consecutively in order to be able to move among them. Then it's rather easy:

[3-Sprint Avg] =
var AverageOverCount = 3
var MaxSprintNo = MAX( Sprints[SprintNo] )
var SprintNoStart = MaxSprintNo - AverageOverCount
var Result =
    calculate(
        countrows( Sprints ) / AverageOverCount,
        SprintNoStart <= Sprints[SprintNo],
        Sprints[SprintNo] < MaxSprintNo,
        REMOVEFILTERS( Sprints[Sprint Name] )
    )
return
    Result

[Anonymous]

Thank you for this!