Trying to get the Sprint Name for the last closed sprint. I can filter, order and head to get the last sprint, but cannot figure out how to get the sprint name. here is what I have so far (converting the results to a string so I can see):
Settostr(
Head(
Order(
Filter(
[Sprint].CurrentHierarchyMember.Children,
[Measures].[Sprint closed?]=“Yes” AND
NOT IsEmpty([Sprint].CurrentMember.get(‘Complete date’)))
,[Sprint].CurrentMember.get(‘Complete date’),DESC)
,1)
)
this is what Is returned:
Hi,
Try creating a calculated measure (with “integer” format) using this formula:
CASE WHEN
[Sprint].CurrentHierarchyMember.name <> "(no sprint)"
AND
[Sprint].CurrentHierarchyMember.name <> "(no board)"
AND
Count(
Filter(
[Sprint].[Sprint].Members,
[Measures].[Sprint closed?]="Yes"
AND
NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))
AND
[Measures].[Issues created]>0
)
)>0
THEN
Order(
Filter(
Descendants([Sprint].CurrentHierarchyMember,[Sprint].[Sprint]),
[Measures].[Sprint closed?]="Yes"
AND
NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))
AND
[Measures].[Issues created]>0
),
CoalesceEmpty([Sprint].CurrentMember.get('Complete date'),""),
DESC
).item(0).name
END
I added 3 conditions to avoid error in the calculation for the empty set.
Martins / eazyBI support
Getting this error
I think it is related to the “.item(0).name” portion
Here you are:
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “DLG_Digital Hub Sprint Report_Average Velocity_Last6Sprints”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[DLG_SprintName]","[Measures].[DLG_SP Committed]","[Measures].[DLG_SP Completed]","[Measures].[Last 6 Closed Sprints Average SP Committed]","[Measures].[Last 6 Closed Sprints Average SP Completed_CompleteDate]","[Measures].[Last 6 Closed Sprints Average SP Completed_EndDate]","[Measures].[Last 6 Closed Sprints Average SP CompletedDividedBySPCommitted]"],“members”:null}]},“rows”:{“dimensions”:[{“name”:“Sprint”,“selected_set”:["[Sprint].[All Sprints]"],“members”:[{“depth”:0,“name”:“All Sprints”,“full_name”:"[Sprint].[All Sprints]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:1,“name”:"(no board)",“full_name”:"[Sprint].[(no board)]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:"(multiple boards)",“full_name”:"[Sprint].[(multiple boards)]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“2WC - 2 Week Challenge”,“full_name”:"[Sprint].[2WC - 2 Week Challenge]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“Chat (My Policy / Quote) ATI”,“full_name”:"[Sprint].[Chat (My Policy / Quote) ATI]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“CMA (new)”,“full_name”:"[Sprint].[CMA (new)]",“drillable”:true,“expanded”:false,“drilled_into”:false,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“CSR board”,“full_name”:"[Sprint].[CSR board]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“DSSC board”,“full_name”:"[Sprint].[DSSC board]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“E2E Paperless”,“full_name”:"[Sprint].[E2E Paperless]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“Enhancements R1 2020”,“full_name”:"[Sprint].[Enhancements R1 2020]",“drillable”:true,“expanded”:false,“drilled_into”:false,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“Insurance Portal (189)”,“full_name”:"[Sprint].[Insurance Portal (189)]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“Mobile Development \u0026 Testing (ScrumBoard)”,“full_name”:"[Sprint].[Mobile Development \u0026 Testing (ScrumBoard)]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“My Policy Chat”,“full_name”:"[Sprint].[My Policy Chat]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“My Policy Cloud”,“full_name”:"[Sprint].[My Policy Cloud]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“PBE - My Policy”,“full_name”:"[Sprint].[PBE - My Policy]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“Quote”,“full_name”:"[Sprint].[Quote]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“RPD board”,“full_name”:"[Sprint].[RPD board]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“Team JDI”,“full_name”:"[Sprint].[Team JDI]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“UBI Ops Scrum”,“full_name”:"[Sprint].[UBI Ops Scrum]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"}],“bookmarked_members”:null}]},“pages”:{“dimensions”:null},“options”:{“nonempty”:“rows”},“view”:{“current”:“table”,“maximized”:false,“table”:{“row_dimension_headers”:{“Sprint”:“Team Board”}}}}
} ],
“calculated_members”: [{“name”:“Sprint closed?”,“dimension”:“Measures”,“format_string”:"",“annotations”:{“group”:“Agile”,“predefined”:true},“formula”:“CASE [Sprint].CurrentMember.getBoolean(‘Closed’)\nWHEN CBool(1) THEN ‘Yes’\nWHEN CBool(0) THEN ‘No’\nEND”},{“name”:“Sprint Story Points committed”,“dimension”:“Measures”,“format_string”:"#,##0.00",“annotations”:{“group”:“Agile”,“predefined”:true},“formula”:"( [Measures].[Story Points added],\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 =\u003e Active],\n [Time].CurrentHierarchy.Levels(‘Day’).DateMember(\n [Sprint].CurrentMember.get(‘Start date’)\n )\n)"},{“name”:“Sprint Story Points at closing”,“dimension”:“Measures”,“format_string”:"#,##0.00",“annotations”:{“group”:“Agile”,“predefined”:true},“formula”:"-- Story points from issues when a sprint was closed\n( [Measures].[Story Points added],\n [Transition Field].[Sprint status],\n [Sprint Status].[Closed],\n [Issue Sprint Status Change].[Active =\u003e Closed]\n)"},{“name”:“Sprint Story Points completed”,“dimension”:“Measures”,“format_string”:"#,##0.00",“annotations”:{“group”:“Agile”,“predefined”:true},“formula”:"(\n [Measures].[Sprint Story Points at closing],\n [Transition Status.Category].[Done]\n)"},{“name”:“Last 6 Closed Sprints Average SP Completed_CompleteDate”,“dimension”:“Measures”,“formula”:“Avg(\nHead(\nOrder(\nFilter(\n[Sprint].CurrentHierarchyMember.Children,\n[Measures].[Sprint closed?]=“Yes” AND\nNOT IsEmpty([Sprint].CurrentMember.get(‘Complete date’)))\n,[Sprint].CurrentMember.get(‘Complete date’),DESC)\n,6)\n, [Measures].[Sprint Story Points completed])”,“format_string”:""},{“name”:“Last 6 Closed Sprints Average SP Committed”,“dimension”:“Measures”,“formula”:“Avg(\nHead(\nOrder(\nFilter(\n[Sprint].CurrentHierarchyMember.Children,\n[Measures].[Sprint closed?]=“Yes” AND\nNOT IsEmpty([Sprint].CurrentMember.get(‘Complete date’)))\n,[Sprint].CurrentMember.get(‘Complete date’),DESC)\n,6)\n, [Measures].[Sprint Story Points committed])”,“format_string”:""},{“name”:“Last 6 Closed Sprints Average SP CompletedDividedBySPCommitted”,“dimension”:“Measures”,“formula”:"[Measures].[Last 6 Closed Sprints Average SP Completed_CompleteDate]/\n[Measures].[Last 6 Closed Sprints Average SP Committed]",“format_string”:"#0.00%"},{“name”:“DLG_SP Completed”,“dimension”:“Measures”,“formula”:“Avg(\nHead(\nOrder(\nFilter(\n[Sprint].CurrentHierarchyMember.Children,\n[Measures].[Sprint closed?]=“Yes” AND\nNOT IsEmpty([Sprint].CurrentMember.get(‘Complete date’)))\n,[Sprint].CurrentMember.get(‘Complete date’),DESC)\n,1)\n, [Measures].[Sprint Story Points completed])”,“format_string”:""},{“name”:“DLG_SP Committed”,“dimension”:“Measures”,“formula”:“Avg(\nHead(\nOrder(\nFilter(\n[Sprint].CurrentHierarchyMember.Children,\n[Measures].[Sprint closed?]=“Yes” AND\nNOT IsEmpty([Sprint].CurrentMember.get(‘Complete date’)))\n,[Sprint].CurrentMember.get(‘Complete date’),DESC)\n,1)\n, [Measures].[Sprint Story Points committed])”,“format_string”:""},{“name”:“DLG_SprintName”,“dimension”:“Measures”,“formula”:“CASE WHEN\n[Sprint].CurrentHierarchyMember.name \u003c\u003e “(no sprint)”\nAND\n[Sprint].CurrentHierarchyMember.name \u003c\u003e “(no board)”\nAND\nCount(\nFilter(\n [Sprint].[Sprint].Members,\n [Measures].[Sprint closed?]=“Yes” \n AND\n NOT IsEmpty([Sprint].CurrentMember.get(‘Complete date’))\n AND\n [Measures].[Issues created]\u003e0\n )\n)\u003e0\nTHEN\nOrder(\nFilter(\nDescendants([Sprint].CurrentHierarchyMember,[Sprint].[Sprint]),\n[Measures].[Sprint closed?]=“Yes” \nAND\nNOT IsEmpty([Sprint].CurrentMember.get(‘Complete date’))\nAND\n[Measures].[Issues created]\u003e0\n),\nCoalesceEmpty([Sprint].CurrentMember.get(‘Complete date’),”"),\nDESC\n).item(0).name\nEND",“format_string”:"#,##0"},{“name”:“Last 6 Closed Sprints Average SP Completed_EndDate”,“dimension”:“Measures”,“formula”:“Avg(\nHead(\nOrder(\nFilter(\n[Sprint].CurrentHierarchyMember.Children,\n[Measures].[Sprint closed?]=“Yes” AND\nNOT IsEmpty([Sprint].CurrentMember.get(‘End date’)))\n,[Sprint].CurrentMember.get(‘End date’),DESC)\n,6)\n, [Measures].[Sprint Story Points completed])”,“format_string”:""}]
}
Thanks.
Try this code for the sprint name formula:
Generate(
Order(
Filter(
Descendants([Sprint].CurrentHierarchyMember,[Sprint].[Sprint]),
[Measures].[Sprint closed?]="Yes"
AND
NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))
AND
[Measures].[Issues created]>0
),
CoalesceEmpty([Sprint].CurrentMember.get('Complete date'),""),
DESC
).item(0),
[Sprint].CurrentMember.name,","
)
Martins
That worked. You are always most helpful.
I always take your solution(s) and then break them down to understand why it works and how.
Thank you Martins.
David