Forum Discussion
Filter only last 5 Sprints
Hey All,
I am trying to create a yes no calculated column that looks at the last 5 finished sprints only so I can create a average closed effort (velocity) for forecasting furture work remaining.
My report is set up with an Iteration Table with all of the sprint details, which is linked to a Work Items table which has all of the work items , story points, state etc. No matter what I try I keep running into errors. Is there a way that I can get this to work automatically, so i don't have to manually filter out sprints?
Iteration Table:
Existing formula that I want to adjust to only average using the 5 latest sprint:
Hey everyone,
Thank you for your help, I have a working solution for this that I have manually validated.
Step 1: Add Past column to Iteration Table
Past = IF( TODAY() >=[EndDate], "1", "0")Step 2: Add is Last 5 column to Iteration Table (note Number of Sprints - VelRoll is changed to 4, this shows as the last 5 sprints)
Is Last 5 = VAR FilteredTable = FILTER ( 'Iterations_table', 'Iterations_table'[Past] = 1) VAR SprintNumTable = ADDCOLUMNS ( FilteredTable, "@SprintNum", VALUE ( 'Iterations_table'[Sprint Number] ) ) VAR LatestSprint = MAXX ( SprintNumTable, [@SprintNum] ) VAR VelRoll = 4 VAR minspint = LatestSprint - VelRoll RETURN IF ( VALUE ( 'Iterations_table'[Sprint Number] ) >= minspint, "Yes", "No" )Step 3: Create measure to calculate average effort closed per last 5 sprint
Avg effort Closed last 5 Sprint = AVERAGEX( KEEPFILTERS(VALUES('Iterations_table'[IterationName])), CALCULATE([Closed Effort], 'Iterations_table'[Is Last 5] = "yes", 'WorkItemType'[WorkItemType] = "User Story" ))Step 4: Create measure to calculate Total Effort Remaining
remaining Effort User Stories = CALCULATE([Remaining Effort], WorkItems[WorkItemType] = "User Story")Step 5: Create measure to calculate sprints required to finish remaining effort (story points) based on Average Effort Last 5 Sprints
Sprints Remaining Rolling Velocity = DIVIDE( [remaining Effort User Stories], [Avg effort Closed last 5 Sprint] )
Step 6: Create measure to calculate current sprint numberCurrent Sprint Number = CALCULATE(Sum(Iterations_table[Sprint Number]), Iterations_table[Is Current Sprint] = "✓")
Step 7: Create measure to calculate estimated sprint finishSprint Finish by Rolling Velocity = [Current Sprint Number] + [Sprints Remaining Rolling Velocity]
16 Replies
- tamerj1
Community Champion
Hi ZedPrince
please try
Is Last 5 = VAR LatestSprint = CALCULATE ( MAX ( 'Iterations table'[Sprint Number] ), 'Iterations table'[IsEnded] = "True" ) VAR VelRoll = 5 VAR minspint = LatestSprintan - VelBall RETURN IF ( 'Iterations table'[Sprint Number] >= minspint, "Yes", "No" )Hey there,
Unfortunately I am still returning an error.
- tamerj1
ZedPrince
Seems you have an issue with the data type of the [Spring Number] column. Is better to fix it in power query but you mau also tryIs Last 5 = VAR LatestSprint = CALCULATE ( MAX ( VALUE ( 'Iterations table'[Sprint Number] ) ), 'Iterations table'[IsEnded] = "True" ) VAR VelRoll = 5 VAR minspint = LatestSprintan - VelBall RETURN IF ( VALUE ( 'Iterations table'[Sprint Number] ) >= minspint, "Yes", "No" )
- tamerj1
ZedPrince
Please try the followingIs Last 5 = VAR FilteredTable = FILTER ( 'Iterations table', 'Iterations table'[IsEnded] = "True" ) VAR SprintNumTable = ADDCOLUMNS ( FilteredTable, "@SprintNum", VALUE ( 'Iterations table'[Sprint Number] ) ) VAR LatestSprint = MAXX ( SprintNumTable, [@SprintNum] ) VAR VelRoll = 5 VAR minspint = LatestSprintan - VelBall RETURN IF ( VALUE ( 'Iterations table'[Sprint Number] ) >= minspint, "Yes", "No" )Hey tamerj1 ,
I did try it and I still get the same errors.
I changed the True/False to reflect a number and it now somewhat works.It is returning sprints that have not yet occured as Yes
Is Last 5 = VAR FilteredTable = FILTER ( 'Iterations_table', 'Iterations_table'[IsEnded Value] = 1) VAR SprintNumTable = ADDCOLUMNS ( FilteredTable, "@SprintNum", VALUE ( 'Iterations_table'[Sprint Number] ) ) VAR LatestSprint = MAXX ( SprintNumTable, [@SprintNum] ) VAR VelRoll = 5 VAR minspint = LatestSprint - VelRoll RETURN IF ( VALUE ( 'Iterations_table'[Sprint Number] ) >= minspint, "Yes", "No" )This is the formula that gives the result so far.
Hey everyone,
Thank you for your help, I have a working solution for this that I have manually validated.
Step 1: Add Past column to Iteration Table
Past = IF( TODAY() >=[EndDate], "1", "0")Step 2: Add is Last 5 column to Iteration Table (note Number of Sprints - VelRoll is changed to 4, this shows as the last 5 sprints)
Is Last 5 = VAR FilteredTable = FILTER ( 'Iterations_table', 'Iterations_table'[Past] = 1) VAR SprintNumTable = ADDCOLUMNS ( FilteredTable, "@SprintNum", VALUE ( 'Iterations_table'[Sprint Number] ) ) VAR LatestSprint = MAXX ( SprintNumTable, [@SprintNum] ) VAR VelRoll = 4 VAR minspint = LatestSprint - VelRoll RETURN IF ( VALUE ( 'Iterations_table'[Sprint Number] ) >= minspint, "Yes", "No" )Step 3: Create measure to calculate average effort closed per last 5 sprint
Avg effort Closed last 5 Sprint = AVERAGEX( KEEPFILTERS(VALUES('Iterations_table'[IterationName])), CALCULATE([Closed Effort], 'Iterations_table'[Is Last 5] = "yes", 'WorkItemType'[WorkItemType] = "User Story" ))Step 4: Create measure to calculate Total Effort Remaining
remaining Effort User Stories = CALCULATE([Remaining Effort], WorkItems[WorkItemType] = "User Story")Step 5: Create measure to calculate sprints required to finish remaining effort (story points) based on Average Effort Last 5 Sprints
Sprints Remaining Rolling Velocity = DIVIDE( [remaining Effort User Stories], [Avg effort Closed last 5 Sprint] )
Step 6: Create measure to calculate current sprint numberCurrent Sprint Number = CALCULATE(Sum(Iterations_table[Sprint Number]), Iterations_table[Is Current Sprint] = "✓")
Step 7: Create measure to calculate estimated sprint finishSprint Finish by Rolling Velocity = [Current Sprint Number] + [Sprints Remaining Rolling Velocity]How do i create the [remaining effort] measure?
Hey Leofolina1985, I can't remember exactly what the formula was, but essential it was a calculation that looked at the estimated effort - closed effort so far. I believe for this particular project we captured effort as story points, and 1 story point = 1 hr tof effort.
- v-yueyunzh-msft
Community Support
Hi, ZedPrince
According to your description You have sorted by date, and generated the [Sprint Number] field, you want to filter out 5 prints from the last end date ([IsEnded=True]). Right?
Here are the steps you can follow：
(1)This is my test data:
(2)We can clilck “New Column” to create a calculated column : “rank_true”
rank_true = VAR _pre_table_true = FILTER ( 'Sheet3', 'Sheet3'[Index] < EARLIER ( 'Sheet3'[Index] ) && 'Sheet3'[IsEnded] = TRUE () ) RETURN IF ( 'Sheet3'[IsEnded] = FALSE (), -1, COUNTROWS ( _pre_table_true ) + 1 )(3)We can create another calculated column: “Is Current Sprint”
Is Current Sprint = VAR _max_rank = MAX ( 'Sheet3'[rank_true] ) VAR _index = 5 RETURN IF ( 'Sheet3'[IsEnded] = FALSE (), "NO", IF ( _max_rank - 'Sheet3'[rank_true] < _index, "YES", "NO" ) )(4)Then we can meet your need , the result is as follows:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
