Sum of stories with story points, in the ready status EXCLUDING issues in an active sprint

Questions & Answers
, ,
1

I have been at this one for a while and I keep getting the same count with different calculations, all of them still including the current sprints worth of sized stories. What am I missing?? Cheers!

Sum(Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Status] matches 'Ready'), 
CASE WHEN 
  [Measures].[Story Points created] > 0 AND
  IsEmpty([Sprint].CurrentMember.get('Start date'))
THEN 
 NonZero([Measures].[Story Points created])
END)
2

Bump :slight_smile:
@martins.vanags @daina.tupule

3

Hi Bryan
Usually the measure ā€œIssue statusā€ is being used to filter issues in a specific status.

Sum(Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Issue Status] = "Ready"), 
CASE WHEN 
  [Measures].[Story Points created] > 0 AND
  IsEmpty([Sprint].CurrentMember.get('Start date'))
THEN 
 NonZero([Measures].[Story Points created])
END)

But If you also filter the calculation by [Sprint].currentmember we would need to see the report definition to understand this use-case better.
Please export and share it here.
By sharing it you wonā€™t share the results of your report - just itā€™s structure (description of columns, rows etc.)

Martins / eazyBI support

4

Thanks @martins.vanags!

I am doing a report on team backlog health. For stories to count towards a healthy backlog they need to have story points and be in the ā€˜readyā€™ status. Then itā€™s a traditional calculation of the teams average velocity per sprint times 2 (2 sprints being the goal for the number of total story points ready in a teams backlog.)

The issue is that I can not find a way to exclude stories that match the criteria above but are already committed in an active sprint.

Here is the definition of the report as you requested:

{
  "cube_name": "Platform Delivery Data",
  "cube_reports": [ {
     "name": "Scrum Teams | Backlog Health | Platform",
     "result_view": "table",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Avg Sprint Velocity]","[Measures].[Groomed Story Points]","[Measures].[Average age days]","[Measures].[Average age workdays]","[Measures].[Groomed Sprints]","[Measures].[Backlog Health %]"],"members":[]}]},"rows":{"dimensions":[{"name":"Team(TIR Official)","selected_set":["[Team(TIR Official)].[SCRUM Platform Teams]"],"members":[{"depth":0,"name":"SCRUM Platform Teams","full_name":"[Team(TIR Official)].[SCRUM Platform Teams]","calculated":true,"drillable":true,"drilled_into":false,"expanded":true}],"bookmarked_members":[{"depth":0,"name":"How We Ship - Scrum","full_name":"[Team(TIR Official)].[How We Ship - Scrum]","calculated":true,"drillable":true}]}],"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[All Projects]","[Project].[EASE Platform Shared Services]","[Project].[Platform Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all","expanded":true,"drilled_into":false,"removed":true},{"depth":0,"name":"Platform Projects","full_name":"[Project].[Platform Projects]","calculated":true,"drillable":true}],"bookmarked_members":[{"depth":1,"name":"EASE Platform CORE","full_name":"[Project].[EASE Platform CORE]","drillable":true,"key":"EPC"},{"depth":1,"name":"EASE Platform Shared Services","full_name":"[Project].[EASE Platform Shared Services]","drillable":true,"key":"EPSS"},{"depth":1,"name":"Caffeine - Mobile CICD Platform (iOS and Android)","full_name":"[Project].[Caffeine - Mobile CICD Platform (iOS and Android)]","drillable":true,"key":"CAF"}],"current_page_members":["[Project].[Platform Projects]"]},{"name":"Time","selected_set":["[Time].[Current Year]"],"members":[{"depth":0,"name":"Current Year","full_name":"[Time].[Current Year]","calculated":true,"drillable":true}],"bookmarked_members":[],"current_page_members":["[Time].[Current Year]"]},{"name":"Team(TIR Official)","duplicate":true,"selected_set":["[Team(TIR Official)].[Y-wing]","[Team(TIR Official)].[SCRUM Platform Teams]"],"members":[{"depth":0,"name":"SCRUM Platform Teams","full_name":"[Team(TIR Official)].[SCRUM Platform Teams]","calculated":true,"drillable":true,"expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Team(TIR Official)].[SCRUM Platform Teams]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{"cell_formatting":{"[Measures].[Backlog Health %]":[{"max":"24.999%","background_color":"#AA4643"},{"background_color":"#DB843D","min":"25.000%","max":"49.9999%"},{"min":"50.000%","background_color":"#DBC63D","max":"79.9999%"},{"min":"80.000%","background_color":"#89A54E"}],"[Measures].[Groomed Sprints]":[{"min":"2.0000","background_color":"#89A54E"}]},"freeze_header":false}},"description":"## Platform Backlog Health\n\n**Goal is for teams to maintain 2 sprints worth of stories that are sized and in the \"Ready\" status**\nAverage age days - average number of days of all open issues"}
  } ],
  "calculated_members": [{"dimension":"Measures","name":"Average age days","format_string":"#,##0.00","formula":"DateDiffDays(\n  TimestampToDate(\n    [Measures].[Issues due created timestamp] /\n    [Measures].[Issues due]\n  ),\n  Now()\n)","annotations":{"group":"Default","predefined":true}},{"dimension":"Measures","name":"Average age workdays","format_string":"#,##0.00","formula":"DateDiffWorkdays(\n  TimestampToDate(\n    [Measures].[Issues due created timestamp] /\n    [Measures].[Issues due]\n  ),\n  Now()\n)","annotations":{"group":"Default","predefined":true}},{"name":"Avg Sprint Velocity","dimension":"Measures","formula":"Avg(Filter(\n  Descendants({[Sprint].CurrentMember,\n    ChildrenSet([Sprint].CurrentMember)}, [Sprint].[Sprint]),\n    [Measures].[Story Points Closed] \u003e 0 AND\n    [Sprint].CurrentMember.Name MATCHES '^EPSS.*|^EPC.*'),\n  [Measures].[Story Points Closed]\n)","format_string":""},{"name":"Groomed Story Points","dimension":"Measures","formula":"Sum(\n  [Status].[Ready],\n  [Measures].[Story Points created]\n)\n\n","format_string":"#,##0"},{"name":"Groomed Sprints","dimension":"Measures","formula":"CASE WHEN\n[Measures].[Avg Sprint Velocity]\u003e0\nOR\n[Measures].[Groomed Story Points]\u003e0\nTHEN\n[Measures].[Groomed Story Points]/[Measures].[Avg Sprint Velocity]\nELSE\n0\nEND\n\n\n\n\n","format_string":"#,##0.00"},{"name":"Backlog Health %","dimension":"Measures","formula":"CASE WHEN\n[Measures].[Avg Sprint Velocity]\u003e0\nOR\n[Measures].[Groomed Story Points]\u003e0\nTHEN\n[Measures].[Groomed Sprints] / 2\nELSE\n0\nEND","format_string":"#0.00%"},{"name":"Current Year","dimension":"Time","formula":"Aggregate({\n  [Time].[Year].CurrentDateMember\n})","format_string":""},{"name":"SCRUM Platform Teams","dimension":"Team(TIR Official)","formula":"Aggregate({\n[Team(TIR Official)].[How We Build - Scrum],\n[Team(TIR Official)].[How We Ship - Scrum]\n})","format_string":""},{"name":"How We Build - Scrum","dimension":"Team(TIR Official)","formula":"Aggregate(\n  {\n  [Team(TIR Official)].[Silverbolt],\n  [Team(TIR Official)].[Skydive],\n  [Team(TIR Official)].[X-wing],\n  [Team(TIR Official)].[Panama],\n  [Team(TIR Official)].[Enki],\n  [Team(TIR Official)].[Athena]\n  }\n)","format_string":""},{"name":"How We Ship - Scrum","dimension":"Team(TIR Official)","formula":"Aggregate({\n  [Team(TIR Official)].[Blitzwing],\n  [Team(TIR Official)].[Cadence],\n  [Team(TIR Official)].[EASE TechX],\n  [Team(TIR Official)].[FireWatch],\n  [Team(TIR Official)].[SpyderCo]\n})","format_string":""},{"name":"Platform Projects","dimension":"Project","formula":"Aggregate({\n[Project].[EASE Platform CORE],\n[Project].[EASE Platform Shared Services],\n[Project].[Caffeine - Mobile CICD Platform (iOS and Android)]\n}\n)","format_string":""}]
}

Thank you as always for the support!

5

Hi,

If you donā€™t use a ā€œSprintā€ dimension in the report it is no sense to use [Sprint].currentmember.get(ā€˜Start dateā€™) as it would not return anything.

If ā€œSprintā€ dimension is not used in the report, the current member expression in such calculation by default returns takes a member ā€œAll sprintsā€ and it has no value in ā€œStart dateā€ property.

I think you can try filtering issues by Issue sprint status property:

Nonzero(
Sum(Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Issue Status] = "Ready"
AND
[Measures].[Issue Sprint Status] <> "Active"
AND
[Measures].[Story Points created] > 0
), 
Measures].[Story Points created]
))

Martins / eazyBI support

1 Like
6

Thank you Martin! The issue was I did not know how to filter the issue sprint status correctly.

Cheers!