Filter only last 5 Sprints

[ZedPrince]

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:

[ZedPrince · Accepted solution]

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 number

Current Sprint Number = CALCULATE(Sum(Iterations_table[Sprint Number]), Iterations_table[Is Current Sprint] = "✓")

Step 7: Create measure to calculate estimated sprint finish

Sprint Finish by Rolling Velocity = [Current Sprint Number] + [Sprints Remaining Rolling Velocity]

[Leofolina1985]

How do i create the [remaining effort] measure?

[ZedPrince]

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]

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

[tamerj1]

@ZedPrince 
Please try the following

Is 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" )

[tamerj1]

@ZedPrince 

Have you tried this solution? ☝️

[ZedPrince]

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. 

[tamerj1]

HI @ZedPrince 
So [IsEnded] column is boolean data type. Working blindly is not so fun 🙂

You can use

Is 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" )

*Edit

Sorry, I just read your complete explanation. Yes the logic need to be modified. we need to filter out the dates that are after today. But which column shall we rely on? as the one in the screenshot is the end date which is expected to be in the future?

[tamerj1]

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" )

[ZedPrince]

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 try

Is 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" )

[ZedPrince]

Hey @tamerj1 
I just tried the last one you suggested and I still came across an error.

Is Last 5 v2 = 
VAR LatestSprint =
    CALCULATE (
        MAX ( VALUE ( 'Iterations_table'[Sprint Number] ) ),
        'Iterations_table'[IsEnded] = "True"
    )
VAR VelRoll = 5
VAR minspint = LatestSprint - VelRoll
RETURN
    IF ( VALUE ( 'Iterations_table'[Sprint Number] ) >= minspint, "Yes", "No" )

In power query, the Sprint Number column is set as a whole number and I have just replaced null with 0. This doesn't seem to of changed anything. I also have duplicated the IsEnded Column, and replaced values with 1 for True, 0 for False. This returns the following.

Is Last 5 = 
VAR LatestSprint =
    CALCULATE (
        MAX ( 'Iterations_table'[Sprint Number] ),
        'Iterations_table'[IsEnded Value] = "1"
    )
VAR VelRoll = 5
VAR minspint = LatestSprint - VelRoll

RETURN
    IF ( 'Iterations_table'[Sprint Number] >= minspint, "1", "0" )

[tamerj1]

@ZedPrince 

Is Last 5 =
VAR LatestSprint =
    CALCULATE (
        MAXX ( 'Iterations table', 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" )

[ZedPrince]

Hey @tamerj1 
It still comes back with the same errors.

[tamerj1]

@ZedPrince 
Can you please paste a screenshot?

[ZedPrince]

Hey @tamerj1 

Here is the error

I get a similar error if I change it to using a numerical representation of the true/false for isended.