Limit Sprints listed with a board view

[briantaylor]

I have a team that has a repot that is built on the board view - and show sprints under the board. I want to limit the number of sprints shown with the board. I tried to filter not the Sprint Create date but loose the board names on the left and only get sprints

Is there a way to fitly on the sprits within the board definition

Aggregate({
[Sprint].[Shared Services Team 1 Board],
[Sprint].[Shared Services Team 2 Board],
[Sprint].[Shared Services Team 3 Board],
[Sprint].[Platform Engineering Board]
})

Here is the full report

{ "cube_name": "Issues", "cube_reports": [ { "name": "Sprint Commitment Stats", "folder_name": "Jay Smith", "result_view": "table", "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Sprint issues committed]","[Measures].[Completed Commitment]","[Measures].[Sprint issues added]","[Measures].[Sprint issues completed]","[Measures].[% Issues Completed]","[Measures].[% Committed Issues Completed]","[Measures].[Issues created]"],"members":[]}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[Boards]"],"members":[{"depth":0,"name":"Boards","full_name":"[Sprint].[Boards]","calculated":true,"drillable":true,"expanded":true,"drilled_into":false,"removed":true}],"bookmarked_members":[]}],"nonempty_crossjoin":true},"pages":{"dimensions":[]},"options":{"nonempty":"rows"},"view":{"current":"table","maximized":false,"table":{"row_dimension_headers":{"Sprint":true}}}} } ], "calculated_members": [{"name":"Sprint issues committed","dimension":"Measures","format_string":"#,##0","annotations":{"group":"Agile"},"formula":"( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Active],\n -- An issue was in a sprint at a sprint start time\n [Issue Sprint Status Change].[Future => Active],\n [Time].CurrentHierarchy.Levels('Day').DateMember(\n [Sprint].CurrentMember.get('Start date')\n )\n)"},{"name":"Sprint issues added","dimension":"Measures","format_string":"#,##0","annotations":{"group":"Agile"},"formula":"( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Active],\n -- An issue was added or created in an active sprint\n [Issue Sprint Status Change].[(none) => Active]\n)"},{"name":"Sprint issues at closing","dimension":"Measures","format_string":"#,##0","annotations":{"group":"Agile"},"formula":"( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Closed],\n -- An issue was in a sprint at closing\n [Issue Sprint Status Change].[Active => Closed]\n)"},{"name":"Sprint issues completed","dimension":"Measures","format_string":"#,##0","annotations":{"group":"Agile"},"formula":"(\n [Measures].[Sprint issues at closing],\n [Transition Status.Category].[Done]\n)"},{"name":"Boards","dimension":"Sprint","formula":"Aggregate({\n [Sprint].[Shared Services Team 1 Board],\n [Sprint].[Shared Services Team 2 Board],\n [Sprint].[Shared Services Team 3 Board],\n [Sprint].[Platform Engineering Board]\n})","format_string":""},{"name":"Completed Commitment","dimension":"Measures","formula":"NonZero(Count(\nFilter(\nDescendants([Issue].CurrentMember, [Issue].[Issue]),\n[Measures].[Sprint issues committed] > 0\nAND\n[Measures].[Sprint issues at closing] > 0\n)\n))","format_string":""},{"name":"% Committed Issues Completed","dimension":"Measures","formula":"CASE WHEN [Measures].[Sprint Issues Committed] > 0 AND [Measures].[Sprint Issues completed] > 0 THEN\n[Measures].[Completed Commitment] / [Measures].[Sprint issues committed]\nEND","format_string":"#0.00%"},{"name":"% Issues Completed","dimension":"Measures","formula":"CASE WHEN [Measures].[Sprint Issues Committed] > 0 AND [Measures].[Sprint Issues completed] > 0 THEN\n[Measures].[Sprint Issues completed] / [Measures].[Sprint Issues committed]\nEND","format_string":"#0%"}] }

[martins.vanags]

Hi Brian,

Indeed, if you create a calculated member in “Sprint” dimension on sprint level boards would not be displayed.
Hoever if you approach board level in the calculated member definition, all sprints would be displayed when board is expanded in the report (you can’t limit that).
One way would be by creating a new calculated measure which later is used to filter sprints that are displayed.

But then more information from you would be needed on the filtering conditions.

Could you share more details how exactly would you filter sprints you would like to show under each board? would it be perhaps sorted list (then ASC or DESC and by which parameter?) of sprints which are displayed only when you expand the board in the report?
And how do you expect the total “Board” level result to be affected? Should it change according to filtered list?

Martins / eazyBI support

[lpattontr]

I have a similar question. I would like to get an aggregate list of the most recent n sprints from a list of boards.
Board A: 90% Average story points completed last 3 sprints
Board B: 75% Average story points completed last 3 sprints

[martins.vanags]

Hi,

Try creating a new calculated measure (with % formatting) to calculate the average value in % (completed/commited) from last 3 sprints (sorted by sprint end date) using this code:

CASE WHEN
[Sprint].CurrentHierarchyMember.Level.Name = "Board"
THEN
Avg(
Head(
Order(
Filter(
Descendants([Sprint].CurrentHierarchyMember,[Sprint].[Sprint]),
[Measures].[Sprint Story Points completed]>0
),
[Measures].[Sprint end date],
BDESC),
3),
[Measures].[Sprint Story Points completed]/
[Measures].[Sprint Story Points committed]
)
END

Martins / eazyBI support

[lpattontr]

Seems exactly what I was looking for, thank you!

[Valerii_Sverdlik]

Hi! I’m trying to do a very similar thing. I have 9 projects, each with own sprint and boards. I want to show a report with a 5 latest sprints for each of the boards, smth like:
Board 1
B1 Sprint 5 (active)
B1 Sprint 4
B1 Sprint 3
B1 Sprint 2
B1 Sprint 1
Board 2
B2 Sprint 5 (active)
B2 Sprint 4
B2 Sprint 3
B2 Sprint 2
B2 Sprint 1
and so on. When I’m creating an expression like:
Aggregate({
Tail(Order(
[Sprint].[FI - Thunderbolt Team Board],
[Sprint].CurrentMember.get(‘End date’), BASC
), 5)
})
just to get a top 5 sprints with one team(So I’m sorting sprints e.g. by end date but also would like to check that sprint name contains some text e.g. PI4) I see a list of all sprints on the board:
I’m ready to copy-paste expression inside aggregation for other boards.

what I’m doing wrong?

[martins.vanags]

Hi Valeri,

In this case, you could try creating a new calculated measure which calculates the rank for last 4 sprints in each board:

Case when
[Sprint].Currentmember.Level.Name = 'Sprint'
Then
Case when [Sprint].CurrentMember.Name <> '(no sprint)'
Then
NonZero( 
Rank(
[Sprint].CurrentMember,
Tail( Order(Filter(
[Sprint].CurrentMember.Parent.children,
[Measures].[Issues created] >0), 
[Sprint].Currentmember.Get('Start date'), 
BASC)
,5)))
End
Else
[Measures].[Issues created] 
End

Then you can filter the report by this measure and remove it from displayed columns
See attached gif.

Martins / eazyBI team

[Valerii_Sverdlik]

Hi!
thank you for the quick response and a graceful solution.

Is there a way to avoid calculating(or displaying) the statistics for the aggregating row, like here:

[martins.vanags]

Hi,
This approach was just to filter the 5 Sprints for each board and since you filter the report by this measure it can’t be blank for board level.
However, you can filter and then remove this “Last X sprints for each board” column and then create another new calculated measure that returns values only for “Sprint” level.

CASE WHEN
[Sprint].CurrentMember.Name = "Sprint"
THEN
[Measures].[Last X sprints for each board]
END

Martins / eazyBI team

[Valerii_Sverdlik]

Hi,

Thank you, that helps. I hope the last question for today, but related to completely different topic - completed SP calculation. I see the discrepancy to what I see in Jira and in EazyBI. I saw couple of topics on the community about it but seems like no one from them covers the situation.

From Jira report I see 6 closed stories before sprint finished which obviously gives me 32 story points(see picture attached)

when I’m looking into the calculated values of SP resolved and completed and drilling through the issue I see that in EazyBI last issues were not taken into consideration what gives another values. What is the issue here?

[Valerii_Sverdlik]

[Valerii_Sverdlik]

Just found out: we have additional status on “Done” state - Awaiting deployment. Do I need to create a new calculated measure to take it into consideration?

[martins.vanags]

Hi,

Do issues with status “Awaiting Deployment” have resolution date?
Likely the don’t, therefore, the are not included in “Story points resolved” calculation.
Why can’t you use “Sprint story points completed” in your report then?

Martins / eazyBI team

[cpuglisi]

@briantaylor

Aggregate({
[Sprint].[Shared Services Team 1 Board],
[Sprint].[Shared Services Team 2 Board],
[Sprint].[Shared Services Team 3 Board],
[Sprint].[Platform Engineering Board]
})

What if I wanted to list specific Sprint Boards as above, but in addition filter for only the Sprints that closed within the prior month (e.g all sprints that closed in October)?

Thanks!

[martins.vanags]

Hi @briantaylor

in this case, you would filter such report via the measure that add the context for your report.
One of predefined measures is “Sprint complete date” which you could use to find sprints that were completed in previous month

Try this code when creating a new calculated measure (with integer format) to filter rows by this measure and find sprints completed last month.

CASE WHEN
[Sprint].CurrentMember.Level.name = "Sprint"
AND
[Measures].[Sprint closed?]="Yes"
AND
DateInPeriod(
  [Measures].[Sprint complete date],
  [Time].[Month].CurrentdateMember.prevmember
)
THEN
1
END

Best regards,

[cpuglisi]

Thank you for the quick response!

This works well, but when I apply the following measure to the Sprint dimension, it still shows all Sprints, not just the Sprints that closed in the previous month:

Aggregate({
[Sprint].[FMC Screeners Scrum Board],
[Sprint].[FMC Agile Team Scrum Board],
[Sprint].[MCX Agile Team 1 Scrum Board],
[Sprint].[MCX Agile Team 1 Scrum Board]
})

I’d like to be able to show a roll up of only certain boards, and when you expand each board, it shows only the Sprints that closed within the previous month.

Currently, with your code you shared (thanks!) it shows all Sprints for each board when you expand
.

[martins.vanags]

Hi @cpuglisi

In this case, make sure that the report rows are filtered by the new measure column before you expand the Sprint member.
See how to order and filter your report by measures
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Orderandfilterrowsbymeasurevalues

Martins / eazyBI

[cpuglisi]

Thanks again for the prompt and informative response.

Works great - except when I apply a filter on the new measure where row = 1, it removes the aggregate row and only shows the rows that equal 1, which makes sense.

Is there away to keep the aggregation rows (which in this case are limited to only showing boards for certain projects), but still apply a filter on the new measure you helped me create. So essentially, the header dropdown of boards will still show, but when you expand it will only show the rows that equal “1” as defined in the new calculated measure?

Thanks again!

[cpuglisi]

Hi @martins.vanags,

I’ve included an illusration of whta I’m hoping to be able to accomplish:

[martins.vanags]

Hi @cpuglisi

Thanks for the image. It helps to understand the expectation better.
Try this code for the calculated measure:

NonZero(
Count(
Filter(
Descendants([Sprint].CurrentHierarchyMember,[Sprint].[Sprint]),
[Measures].[Sprint closed?]="Yes"
AND
DateInPeriod(
  [Measures].[Sprint complete date],
  [Time].[Month].CurrentdateMember.prevmember
)
AND
[Measures].[Issues created]>0
)
)
)

Martins / eazyBI