Limit Sprints listed with a board view


#1

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%"}] }


#2

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


#3

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


#4

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


#5

Seems exactly what I was looking for, thank you!