I am trying to create a dashboard that shows the following:
- Number of sprint issues committed to
- Number of sprint issues completed of committed
The dashboard would show this data by project, then by time (quarters). If a sprint were to start at the end of a quarter, eg Q1, and complete in the next quarter (eg Q2), the quarter that the sprint started in (Q1) would be given the credit for both measures, even if the work was technically completed in Q2. As an example, if we committed to 10 issues and completed 8 for a sprint that started in Q1 and ended in Q2, Q1 would get credit for the 10 issues committed to and the 8 completed.
Below is my exported definition. The problem is that “TEST Committed issues” is showing data for Q2 when it should be showing in Q1, and no data for “TEST Completed committed issues” is showing up for the sprint that started in Q1 and ended in Q2.
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "O1KR1 - Sprints Completed As Committed - Quarterly Summary",
"folder_name": "2023 Engineering OKRs",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[TEST Completed committed issues]","[Measures].[TEST Committed issues]"],"members":[{"depth":0,"full_name":"[Measures].[TEST Completed committed issues]","format_string":"#,##0","report_specific":true,"name":"TEST Completed committed issues","calculated":true,"id":"[Measures].[TEST Completed committed issues]"},{"depth":0,"full_name":"[Measures].[TEST Committed issues]","format_string":"#,##0","report_specific":true,"name":"TEST Committed issues","calculated":true}]}]},"rows":{"dimensions":[{"name":"Project","selected_set":["[Project].[Infrastructure]"],"members":[],"bookmarked_members":[]},{"name":"Time","selected_set":["[Time].[Current year]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Time].[Quarter])","members":[],"bookmarked_members":[]},{"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},{"depth":1,"name":"INF Board","full_name":"[Sprint].[INF Board]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Sprint].[All Sprints]"}],"bookmarked_members":[]}],"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Project","duplicate":true,"selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"Engineering Initiatives","full_name":"[Project].[Engineering Initiatives]","drillable":true,"key":"EI","removed":true,"parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"Infrastructure","full_name":"[Project].[Infrastructure]","drillable":true,"key":"INF","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"Tests","full_name":"[Project].[Tests]","drillable":true,"key":"TES","removed":true,"parent_full_name":"[Project].[All Projects]"}],"bookmarked_members":[],"current_page_members":["[Project].[Infrastructure]"]},{"name":"Time","duplicate":true,"selected_set":["[Time].[Current year]"],"members":[{"depth":0,"name":"Current year","full_name":"[Time].[Current year]","annotations":{"group":"Default","predefined":"true"},"calculated":true,"drillable":true,"dimension":"Time"}],"bookmarked_members":[],"current_page_members":["[Time].[Current year]"]},{"name":"Issue Type","selected_set":["[Issue Type].[Bugs]"],"members":[{"depth":0,"name":"Bugs","full_name":"[Issue Type].[Bugs]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"Bug","full_name":"[Issue Type].[Bug]","parent_full_name":"[Issue Type].[Bugs]"},{"depth":1,"name":"Planning Task","full_name":"[Issue Type].[Planning Task]","parent_full_name":"[Issue Type].[Bugs]"},{"depth":1,"name":"Report Request","full_name":"[Issue Type].[Report Request]","parent_full_name":"[Issue Type].[Bugs]"},{"depth":1,"name":"Spike","full_name":"[Issue Type].[Spike]","parent_full_name":"[Issue Type].[Bugs]"},{"depth":1,"name":"Story","full_name":"[Issue Type].[Story]","parent_full_name":"[Issue Type].[Bugs]"},{"depth":1,"name":"Task","full_name":"[Issue Type].[Task]","parent_full_name":"[Issue Type].[Bugs]"},{"depth":1,"name":"Technical Design Spike","full_name":"[Issue Type].[Technical Design Spike]","parent_full_name":"[Issue Type].[Bugs]"}],"bookmarked_members":[],"current_page_members":["[Issue Type].[Bug]","[Issue Type].[Planning Task]","[Issue Type].[Report Request]","[Issue Type].[Spike]","[Issue Type].[Story]","[Issue Type].[Task]","[Issue Type].[Technical Design Spike]"]}]},"options":{"nonempty":true},"view":{"current":"table","maximized":false,"table":{"cell_formatting":{}}},"calculated_members":[{"dimension":"Measures","name":"Target %","formula":"0.90","format_string":"#0%"},{"dimension":"Measures","name":"TEST Completed committed issues","formula":"NonZero(Sum(\n Filter(\n Descendants([Issue].Currentmember, [Issue].[Issue]),\n [Measures].[TEST Committed issues] \u003e 0\n ),\n (\n [Measures].[Sprint issues completed],\n [Time].CurrentHierarchy.DefaultMember\n )\n))","format_string":""},{"dimension":"Measures","name":"TEST Committed issues","formula":"CASE WHEN\nNOT IsEmpty([Measures].[Sprint issues completed])\nTHEN\n [Measures].[TEST Committed issues - all time]\nEND","format_string":""},{"dimension":"Measures","name":"TEST Committed issues - all time","formula":"(\n [Measures].[Committed issues],\n [Time].CurrentHierarchyMember,\n [Sprint].CurrentHierarchyMember\n)","format_string":""}]}
} ],
"calculated_members": [{"dimension":"Time","name":"Current year","format_string":"","formula":"Aggregate({\n [Time].[Year].CurrentDateMember\n})"},{"name":"Sprint issues committed","dimension":"Measures","format_string":"#,##0","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 =\u003e Active]\n)\n"},{"name":"Sprint issues at closing","dimension":"Measures","format_string":"#,##0","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 =\u003e Closed]\n)\n"},{"name":"Sprint issues completed","dimension":"Measures","format_string":"#,##0","formula":"(\n [Measures].[Sprint issues at closing],\n [Transition Status.Category].[Done]\n)\n"},{"name":"Committed issues","dimension":"Measures","formula":"-- 2023-04-14 Below is the old way of calculating committed issues\n-- For some reason, it does not pick up on https://higidev.atlassian.net/browse/DAM-5011\n(\n [Resolution].[(unresolved)],\n [Measures].[Sprint issues committed]\n)\n/*(\n [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 =\u003e Active],\n -- Make sure the issue wasn't closed/done prior to the sprint starting\n [Transition Status].[Not resolved]\n)*/","format_string":""},{"name":"Not resolved","dimension":"Transition Status","formula":"Aggregate({\n [Transition Status].[Open],\n [Transition Status].[In Refinement],\n [Transition Status].[Ready for Dev],\n [Transition Status].[In Progress],\n [Transition Status].[Dev Complete],\n [Transition Status].[Ready for QA],\n [Transition Status].[In QA]\n})","format_string":""}]
}