topic Re: Filter only last 5 Sprints in DAX Commands and Tips

Filter only last 5 Sprints

ZedPrince — Mon, 12 Sep 2022 02:15:50 GMT

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:

Re: Filter only last 5 Sprints

tamerj1 — Mon, 12 Sep 2022 03:53:20 GMT

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

Re: Filter only last 5 Sprints

ZedPrince — Mon, 12 Sep 2022 05:50:02 GMT

Hey there,

Unfortunately I am still returning an error.

Re: Filter only last 5 Sprints

tamerj1 — Mon, 12 Sep 2022 06:18:19 GMT

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

Re: Filter only last 5 Sprints

ZedPrince — Mon, 12 Sep 2022 06:30:41 GMT

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

Re: Filter only last 5 Sprints

tamerj1 — Mon, 12 Sep 2022 06:31:00 GMT

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

Re: Filter only last 5 Sprints

ZedPrince — Mon, 12 Sep 2022 06:40:15 GMT

Hey tamerj1
It still comes back with the same errors.

Re: Filter only last 5 Sprints

tamerj1 — Mon, 12 Sep 2022 07:23:41 GMT

ZedPrince
Can you please paste a screenshot?

Re: Filter only last 5 Sprints

tamerj1 — Mon, 12 Sep 2022 08:02:26 GMT

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

Re: Filter only last 5 Sprints

ZedPrince — Mon, 12 Sep 2022 22:37:24 GMT

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.

Re: Filter only last 5 Sprints

tamerj1 — Tue, 13 Sep 2022 02:50:42 GMT

ZedPrince

Have you tried this solution? ☝️

Re: Filter only last 5 Sprints

ZedPrince — Tue, 13 Sep 2022 03:01:45 GMT

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.

Re: Filter only last 5 Sprints

tamerj1 — Tue, 13 Sep 2022 05:16:15 GMT

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?

Re: Filter only last 5 Sprints

v-yueyunzh-msft — Tue, 13 Sep 2022 07:28:24 GMT

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

Re: Filter only last 5 Sprints

ZedPrince — Tue, 13 Sep 2022 23:03:21 GMT

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]

Re: Filter only last 5 Sprints

Leofolina1985 — Thu, 02 May 2024 23:12:50 GMT

How do i create the [remaining effort] measure?

Re: Filter only last 5 Sprints

ZedPrince — Thu, 02 May 2024 23:20:20 GMT

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.