Hi, I would like to have for all my 10 teams a table where for each team I can see for the past 6 sprints average story points committed vs completed and their velocity.
I have a report here but is per sprint, I want it per team
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Sprint velocity chart”,
“folder_name”: “Ways of Working Metrics”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Sprint Story Points committed]”,“[Measures].[Sprint Story Points completed]”,“[Measures].[Sprint running velocity in board]”],“members”:}]},“rows”:{“dimensions”:[{“name”:“Sprint”,“selected_set”:[“[Sprint].[Buran Team Board]”,“[Sprint].[FARU]”,“[Sprint].[Next2K (new)]”,“[Sprint].[TM Silta Team Board]”,“[Sprint].[TM-IDEA TEAM Board]”,“[Sprint].[TyCoon Team Board]”,“[Sprint].[UM-Isotopes Team Board]”,“[Sprint].[UM-TheSixers Team Board]”],“selected_set_expression”:“DescendantsSet({{selected_set}}, [Sprint].[Sprint])”,“members”:,“bookmarked_members”:}],“filter_by”:{“conditions”:[{“expression”:[“[Measures].[Last closed sprints by boards]”],“operator”:“\u003c=”,“value”:“5”}]},“nonempty_crossjoin”:false},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:[“[Project].[All Projects]”],“members”:[{“depth”:0,“drillable”:true,“drilled_into”:false,“expanded”:true,“full_name”:“[Project].[All Projects]”,“name”:“All Projects”,“type”:“all”}],“bookmarked_members”:,“current_page_members”:[“[Project].[All Projects]”]},{“name”:“Sprint”,“duplicate”:true,“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},{“depth”:1,“name”:“Buran Team Board”,“full_name”:“[Sprint].[Buran Team Board]”,“drillable”:true,“parent_full_name”:“[Sprint].[All Sprints]”},{“depth”:1,“name”:“FARU”,“full_name”:“[Sprint].[FARU]”,“drillable”:true,“parent_full_name”:“[Sprint].[All Sprints]”},{“depth”:1,“name”:“Next2K (new)”,“full_name”:“[Sprint].[Next2K (new)]”,“drillable”:true,“parent_full_name”:“[Sprint].[All Sprints]”},{“depth”:1,“name”:“TM Silta Team Board”,“full_name”:“[Sprint].[TM Silta Team Board]”,“drillable”:true,“parent_full_name”:“[Sprint].[All Sprints]”},{“depth”:1,“name”:“TM-IDEA TEAM Board”,“full_name”:“[Sprint].[TM-IDEA TEAM Board]”,“drillable”:true,“parent_full_name”:“[Sprint].[All Sprints]”},{“depth”:1,“name”:“TyCoon Team Board”,“full_name”:“[Sprint].[TyCoon Team Board]”,“drillable”:true,“parent_full_name”:“[Sprint].[All Sprints]”},{“depth”:1,“name”:“UM-Isotopes Team Board”,“full_name”:“[Sprint].[UM-Isotopes Team Board]”,“drillable”:true,“parent_full_name”:“[Sprint].[All Sprints]”},{“depth”:1,“name”:“UM-TheSixers Team Board”,“full_name”:“[Sprint].[UM-TheSixers Team Board]”,“drillable”:true,“parent_full_name”:“[Sprint].[All Sprints]”}],“bookmarked_members”:,“current_page_members”:[“[Sprint].[Buran Team Board]”,“[Sprint].[FARU]”,“[Sprint].[Next2K (new)]”,“[Sprint].[TM Silta Team Board]”,“[Sprint].[TM-IDEA TEAM Board]”,“[Sprint].[TyCoon Team Board]”,“[Sprint].[UM-Isotopes Team Board]”,“[Sprint].[UM-TheSixers Team Board]”]}]},“options”:{“nonempty”:“rows”},“view”:{“current”:“table”,“maximized”:false,“table”:{}},“calculated_members”:[{“dimension”:“Measures”,“name”:“Version releases by Sprints”,“formula”:“CASE WHEN [Sprint].CurrentMember.Level.Name = “Sprint”\nTHEN\nNonEmptyString(Generate(Filter( \n [Fix Version].[Version].Members,\n DateBetween(\n [Fix Version].CurrentMember.getDate(‘Release date’),\n [Sprint].CurrentMember.GetDate(“Start date”),\n [Sprint].CurrentMember.GetDate(“End date”)\n ) AND\n – Version has issues in this board:\n ([Measures].[Issues created], \n [Time].CurrentHierarchy.DefaultMember,\n [Sprint].CurrentMember.Parent) \u003e 0),\n [Fix Version].CurrentMember.Name,\n ', '\n))\nEND”,“format_string”:“”}]}
} ],
“calculated_members”: [{“name”:“Last closed sprints by boards”,“dimension”:“Measures”,“format_string”:“”,“formula”:“CASE\nWHEN\n ([Measures].[Issues created],\n [Sprint].CurrentHierarchyMember.Parent) \u003e 0\nTHEN\n NonZero(\n Rank(\n [Sprint].CurrentHierarchyMember,\n Order(\n Filter(\n [Sprint].CurrentHierarchyMember.Parent.Children,\n NOT isEmpty([Sprint].CurrentHierarchyMember.Get(‘Start date’))\n AND [Sprint].CurrentHierarchyMember.GetBoolean(‘Closed’)\n ),\n [Sprint].CurrentHierarchyMember.Get(‘Start date’), DESC)\n )\n )\nEND\n”},{“name”:“Sprint Story Points committed”,“dimension”:“Measures”,“format_string”:“#,##0.00”,“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)\n”},{“name”:“Sprint Story Points at closing”,“dimension”:“Measures”,“format_string”:“#,##0.00”,“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)\n”},{“name”:“Sprint Story Points completed”,“dimension”:“Measures”,“format_string”:“#,##0.00”,“formula”:“(\n [Measures].[Sprint Story Points at closing],\n [Transition Status.Category].[Done]\n)\n”},{“name”:“Sprint running velocity in board”,“dimension”:“Measures”,“formula”:“-- annotations.group=Sprint: Scope \u0026 Metrics\nCASE WHEN \n [Sprint].CurrentMember.Level.Name = ‘Sprint’ \n AND\n [Sprint].CurrentMember.GetBoolean(‘Closed’)\nTHEN\n Avg(\n – filter last 5 previous sprints in a board starting from current sprint\n Tail(\n Filter(\n {[Sprint].CurrentMember.FirstSibling:[Sprint].CurrentMember},\n [Sprint].CurrentMember.GetBoolean(‘Closed’) \n ),\n – set the count of last closed sprints for running velocity\n 5\n ),\n [Measures].[Sprint Story Points completed]\n )\nEND”,“format_string”:“”}]
}