Burndown Projection Based on Recent Performance

Hi,

We currently have a release burndown report that tracks:

  • Total Points in the release over time (through today)
  • Points remaining in the release (through today)
  • The target burndown based on release lock and total points
  • A projection based on current remaining points and historical performance

This report can then be filtered by team, by legend, by epic etc. The main issue I have with the report as-is is that the going forward projection is based on a Predicted completion date measure that uses a daily average of resolved story points from the start of the version report as metric for prediction of future progress, which could be misleading for several reasons: we may have added more resources during the milestone, we may not have started work on a legend until halfway, through, etc.

Request
I’d like to modify this report so that projections are based on recent performance (e.g., performance in the last 30 days) rather than performance since the start of the version report. What would be the best way for me to accomplish this? Any help would be appreciated.

Current Report Spec

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Release Burndown Report",
     "folder_name": "Ryan WIP",
     "result_view": "timeline_chart",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Target Burndown]","[Measures].[Story Points remaining through Today]","[Measures].[Total Story Points through Today]","[Measures].[Story Point Projection]","[Measures].[Story Points Resolved]","[Measures].[Predicted dates (Story Points)]"],"members":[{"depth":0,"full_name":"[Measures].[Target Burndown]","format_string":"#,##0","report_specific":true,"name":"Target Burndown","calculated":true,"id":"[Measures].[Target Burndown]"},{"depth":0,"full_name":"[Measures].[Story Point Projection]","format_string":"#,##0","report_specific":true,"name":"Story Point Projection","calculated":true},{"depth":0,"full_name":"[Measures].[Story Points Resolved]","format_string":"#,##0","report_specific":true,"name":"Story Points Resolved","calculated":true},{"depth":0,"full_name":"[Measures].[Predicted dates (Story Points)]","format_string":"#,##0","report_specific":true,"name":"Predicted dates (Story Points)","calculated":true}]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time.Weekly].[Week].Members"],"members":[],"bookmarked_members":[]}],"filter_by":{"conditions":[{"expression":["[Measures].[Time in version]"],"operator":"\u003e","value":"0"}]}},"pages":{"dimensions":[]},"options":{},"view":{"current":"timeline_chart","maximized":false,"timeline_chart":{"series_type":"column","stacked":false,"series_options":{"Story Points created":{"type":"column","color":"#DCBCED"},"Open issues":{"type":"line","separateAxis":1,"color":"#2F579C"},"Story points created trend":{"color":"#B31238"},"Due date":{"type":"plotline","dataLabelType":"top vertical"},"Open issues until today":{"color":"#2F579C","type":"spline"},"Expected burndown":{"color":"#2F579C","type":"line-Dash"},"End date milestone":{"type":"plotline","dataLabelType":"top vertical","color":"#00875A"},"Expected open issue burndown":{"color":"#2F579C","type":"line-Dash"},"Open issues trend in future":{"color":"#2F579C","type":"line-Dash"},"Open issues ideal burndown":{"color":"#344563"},"Open issues burndown from today":{"color":"#344563","type":"line-Dash"},"Release Story Points Guideline":{"type":"line-Dash","color":"#5E6C84"},"Predicted dates (Story Points)":{"type":"plotline","color":"#F95E16","dataLabelType":"top vertical"},"Story points remaining through Today":{"color":"#00875A"},"Total Story Points through Today":{"color":"#B31238","type":"line"},"Story Points remaining through Today":{"type":"line","color":"#0093B5"},"Story Points Resolved":{"color":"#00875A"},"Predicted completion line (Story Points)":{"type":"line"},"Story Point Projection":{"type":"line-Dash","color":"#0093B5"},"Story Points (Jira) resolved":{"color":"#00875A"},"Target Burndown":{"color":"#091E42","type":"line-Dash"}},"y_axis":{"0":{"min":"0","title":"Story Points"}},"show_full_caption":["Measures"]},"table":{"freeze_header":true}},"calculated_members":[{"dimension":"Measures","name":"Total Story Points","formula":"[Measures].[Story Points history]","format_string":"#,##0"},{"dimension":"Measures","name":"Target Burndown","formula":"CASE WHEN\n  NOT isEmpty([Measures].[Version report Start Date])\n  Then\n  Case \n  When\n  -- start with total points\n    DateInPeriod([Measures].[Version report Start Date],\n    [Time].CurrentHierarchyMember)\n   OR\n-- apply for planed period\n    DateBetween(\n      [Time].CurrentHierarchyMember.StartDate,\n      [Measures].[Version report Start Date],\n      [Measures].[Release Feature Lock]\n    )\n  Then\n  -- total points to burn\n   ([Measures].[Total Story Points],\n    [Time].CurrentHierarchy.Defaultmember)\n    /\n    -- total days in cycle\n    DateDiffDays(\n      [Measures].[Version report Start Date],\n      [Measures].[Release Feature Lock]\n    )\n    *\n    -- days till the end of cycle\n    DateDiffDays(\n      [Time].CurrentHierarchyMember.StartDate,\n      [Measures].[Release Feature Lock])\n  End\nEND","format_string":"#,##0"},{"dimension":"Measures","name":"Predicted dates (Story Points)","formula":"--annotations.group=Predicted\nCase When\n  DateInPeriod(\n    Now(),\n    [Time].CurrentHierarchyMember)\nThen\n  'Today - ' \n  || + Format(Now(), 'Medium Date')\nWhen\n      DateInPeriod(\n        [Fix Version].CurrentHierarchymember.Get('Release date'),\n        [Time].CurrentHierarchyMember)  \nThen\n   'Release Lock - ' \n  || + Format([Fix Version].CurrentHierarchymember.Get('Release date'), 'Medium Date')\nWHEN\n  DateInPeriod(\n    [Measures].[Release Feature Lock],\n    [Time].CurrentHierarchyMember)\nTHEN\n  'Feature Lock - '\n  || + Format([Measures].[Release Feature Lock], 'Medium Date')\nWhen \n  DateInPeriod(\n    [Measures].[Predicted completion date (Story Points)],\n    [Time].CurrentHierarchyMember)  \nThen\n  Case when\n      DateInPeriod(\n        [Fix Version].CurrentHierarchymember.Get('Release date'),\n        [Time].CurrentHierarchyMember)\n     Then\n       'Predicted/Version date - ' \n      || Format([Measures].[Predicted completion date (Story Points)], 'Medium Date')\n    Else\n  'Predicted date - ' \n  || Format([Measures].[Predicted completion date (Story Points)], 'Medium Date')\n  End\nEnd","format_string":"#,##0"},{"dimension":"Measures","name":"Story Points Resolved","formula":"[Measures].[Story Points (Jira) resolved]","format_string":"#,##0"},{"dimension":"Measures","name":"Story Point Projection","formula":"CASE WHEN\n  DateCompare(now(),\n    [Time].CurrentHierarchyMember.StartDate) \u003c 0\nTHEN \n([Time].CurrentHierarchy.Level.CurrentDateMember,\n  [Measures].[Story Points remaining])\n* DateDiffDays([Time].CurrentHierarchyMember.StartDate,\n   [Measures].[Predicted completion date (Story Points)])\n/ DateDiffDays(Now(),[Measures].[Predicted completion date (Story Points)])\nWHEN\nDateInPeriod(\n  'Today',\n  [Time].CurrentHierarchyMember\n)\nTHEN\nNonZero([Measures].[Story Points remaining])\nEND","format_string":"#,##0"},{"dimension":"Measures","name":"Total Story Points after Today","formula":"Case when\n  -- show any past periods\n  DateBeforePeriodEnd(\n    \"Today\",\n    [Time].CurrentHierarchyMember)\n  OR \n  -- show current period\n  DateInPeriod(\n    \"Today\",\n    [Time].CurrentHierarchyMember\n  )\nThen\n[Measures].[Story Points history]\nEnd","format_string":""},{"dimension":"Measures","name":"Release Feature Lock","formula":"DateParse('26 aug 2022')","format_string":""}],"description":"Remaining Story Points in the release plus projection based on resolved Story Points per day. Remaining Story Points + Projected should trend beneath the Target Burndown line to hit Feature Lock.\nSelect specific Legends and/or Epics for a more targeted burndown of feature efforts."}
  } ],
  "calculated_members": [{"dimension":"Measures","name":"Version release date","format_string":"mmm dd yyyy","formula":"CoalesceEmpty(\n  [Affects Version].CurrentHierarchyMember.get('Release date'),\n  [Fix Version].CurrentHierarchyMember.get('Release date')\n)"},{"name":"Story Points history","dimension":"Measures","format_string":"#,##0.00","formula":"Cache(\n  NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n    [Measures].[Story Points (Jira) change]\n  ))\n  + [Measures].[Story Points (Jira) change]\n)\n"},{"name":"Cumulative Story Points resolved","dimension":"Measures","format_string":"#,##0.00","formula":"Cache(\n  Sum({PreviousPeriods([Time].CurrentHierarchyMember),\n      [Time].CurrentHierarchyMember},\n    [Measures].[Story Points (Jira) resolved]\n  )\n)\n"},{"name":"Predicted completion date (Story Points)","dimension":"Measures","formula":"--annotations.group=Predicted\nCache(\nCase when\n  ([Measures].[Story Points (Jira) resolved],\n   [Time].CurrentHierarchy.DefaultMember) \u003e 0\n   AND\n   ([Measures].[Story Points (Jira) due], \n    [Time].CurrentHierarchy.DefaultMember) \u003e 0\nThen\n  DateAddDays( \"Today\",\n  -- add days to reach this ammount\n  Cache(\n  -- all currently open story points\n    ([Measures].[Story Points (Jira) due], \n     [Time].CurrentHierarchy.DefaultMember)\n      /\n -- divided by days performance\n    Cache(\n      -- currently resolved story points\n       ([Measures].[Story Points (Jira) resolved], \n     [Time].CurrentHierarchy.DefaultMember)\n      /\n      -- days till today\n      DateDiffDays(\n       [Measures].[Version report Start Date], \n       \"Today\")\n    )\n  )\n)\nEnd)","format_string":"mmm dd yyyy"},{"name":"Predicted dates (Story Points)","dimension":"Measures","formula":"--annotations.group=Predicted\nCase When\n  DateInPeriod(\n    Now(),\n    [Time].CurrentHierarchyMember)\nThen\n  'Today - ' \n  || + Format(Now(), 'Medium Date')\nWhen\n      DateInPeriod(\n        [Fix Version].CurrentHierarchymember.Get('Release date'),\n        [Time].CurrentHierarchyMember)  \nThen\n   'Milestone Lock - ' \n  || + Format([Fix Version].CurrentHierarchymember.Get('Release date'), 'Medium Date')\nWhen \n  DateInPeriod(\n    [Measures].[Predicted completion date (Story Points)],\n    [Time].CurrentHierarchyMember)  \nThen\n  Case when\n      DateInPeriod(\n        [Fix Version].CurrentHierarchymember.Get('Release date'),\n        [Time].CurrentHierarchyMember)\n     Then\n       'Predicted/Version date - ' \n      || Format([Measures].[Predicted completion date (Story Points)], 'Medium Date')\n    Else\n  'Predicted date - ' \n  || Format([Measures].[Predicted completion date (Story Points)], 'Medium Date')\n  End\nEnd","format_string":"#,##0"},{"name":"Version report Start Date","dimension":"Measures","formula":"--annotations.group=Predicted\n-- if version has start date it will be used as a Start date, \n-- otherwise first day when Issues with story points were resolved will be treated as Start date\n\n\n--2017-01-01\n\nCache(\nCase when\n  Not isempty([Fix Version].CurrentHierarchyMember.get('Start date'))\nThen\n-- Selected fix versions has Start Date, this date is used as Start Date \n  [Fix Version].CurrentHierarchyMember.get('Start date')\nWhen\n  ([Measures].[Story Points history],\n  [Time].CurrentHierarchy.Defaultmember) \u003e 0\nThen\n-- First date with story points added to Fix version\n\nFilter([Time].CurrentHierarchy.Levels('Day').Members,\n      [Measures].[Story Points history] \u003e 0\n      ).Item(0).StartDate\nEnd)\n","format_string":"mmm dd yyyy"},{"name":"Time in version","dimension":"Measures","formula":"--annotations.group=Predicted\nCase when\n  DateBetween(\n    [Time].CurrentHierarchyMember.StartDate,\n    [Measures].[Version report Start Date],\n    -- latest date - prediction date or release date\n    TimestampToDate(Max(\n      {[Measures].[Version release date],\n       [Measures].[Predicted completion date (Story Points)]\n      },\n      DateToTimestamp([Measures].CurrentMember)\n    ))\n    )\n  or\n  DateInPeriod(\n    [Measures].[Version report Start Date],\n    [Time].CurrentHierarchyMember\n    )\n Then\n 1\n End","format_string":"#,##0"},{"name":"Total Story Points","dimension":"Measures","formula":"[Measures].[Story Points history]","format_string":""},{"name":"Story Points remaining","dimension":"Measures","format_string":"#,##0","formula":"[Measures].[Story Points history] -\n[Measures].[Cumulative Story Points resolved]\n"},{"name":"Story Points remaining through Today","dimension":"Measures","formula":"Case when\n  -- show any past periods\n  DateAfterPeriodEnd(\n    \"Today\",\n    [Time].CurrentHierarchyMember)\n  OR \n  -- show current period\n  DateInPeriod(\n    \"Today\",\n    [Time].CurrentHierarchyMember\n  )\nThen\n[Measures].[Story Points remaining]\nEnd","format_string":"#,##0"},{"name":"Total Story Points through Today","dimension":"Measures","formula":"Case when\n  -- show any past periods\n  DateAfterPeriodEnd(\n    \"Today\",\n    [Time].CurrentHierarchyMember)\n  OR \n  -- show current period\n  DateInPeriod(\n    \"Today\",\n    [Time].CurrentHierarchyMember\n  )\nThen\n[Measures].[Story Points history]\nEnd","format_string":"#,##0"}]
}

@rbelmont
Try this formula for the calculated measure: “Predicted completion date (Story Points)”

--annotations.group=Predicted
Cache(
Case when
  ([Measures].[Story Points (Jira) resolved],
   [Time].CurrentHierarchy.DefaultMember) > 0
   AND
   ([Measures].[Story Points (Jira) due], 
    [Time].CurrentHierarchy.DefaultMember) > 0
Then
  DateAddDays( "Today",
  -- add days to reach this ammount
  Cache(
  -- all currently open story points
    ([Measures].[Story Points (Jira) due], 
     [Time].CurrentHierarchy.DefaultMember)
      /
  -- divided by days performance
    Cache(
      --  resolved story points in last 30 days
      Sum(
  [Time].[Day].DateMembersBetween('30 days ago', 'today'),
  [Measures].[Story Points (Jira) resolved]
  )
      /
      30
  )
)
End)

Martins / eazyBI

Thanks @martins.vanags. I tried this by creating a new calculated measure called "Predicted completion date (Story Points) (Recent Performance). When I first used your formula I got a “Syntax error at line 29, column 1, token ‘End’” error. I was able to eliminate the error by adding another closed parenthesis before End, however I’m getting very strange results:

In this picture I’ve updated “Story Point Projection” to use the new predicted completion date formula instead of the old one (and included both predicted date measures to troubleshoot), and it seems like something isn’t working properly with the new formula. Would love to get your thoughts.

Thanks,
Ryan

@martins.vanags tinkering with this a bit more, I think I see the issue (but not sure of the solution). When I create a new measure that just tries to capture resolved story points in the last 30 days (“SP Resolved Last 30 Days”) I get this:

It looks like it’s just showing the story points resolved in each period within a 30 day window from today (i.e., W25 partially falls outside the 30 day window so only some of the points are listed compared to “Story Points Resolved”). It should ideally behave like the “AVG Full Release SP” measure, where every period has the same value (the total number of story points resolved between 30 days ago and today).

Any suggestions?

@rbelmont
Which formula did you use to calculate “SP Resolved Last 30 days” in your last screenshot?

Martins

@martins.vanags to clarify I created this measure to try to troubleshoot what was going on. That measure was created with the “resolved story points in last 30 days” component of the formula you provided:

Cache(
  --  resolved story points in last 30 days
  Sum(
    [Time].[Day].DateMembersBetween('30 days ago', 'today'),
    [Measures].[Story Points (Jira) resolved]
   )
)

It must be because of using weekly hierarchy.
Try this code since you use weekly hierarchy in your report.

Cache(
  --  resolved story points in last 30 days
  Sum(
    [Time.weekly].[Day].DateMembersBetween('30 days ago', 'today'),
    [Measures].[Story Points (Jira) resolved]
   )
)

Martins

Thanks @martins.vanags, that solved the issue! A few final queries and I’ll stop nagging you :slight_smile:

Here is the report now with the suggested change made, filtered only by milestone:

And here is the report filtered by milestone and to a specific Legend (hierarchically above Epic):

  1. How do I stop “Story Point Projection” at 0, rather than having it drop into negative numbers? In the first report image I would expect that “Story Point Projection” stops at 0 in W41 instead of -239. The current config for that measure is:
CASE WHEN
  DateCompare(now(),
    [Time].CurrentHierarchyMember.StartDate) < 0
THEN 
([Time].CurrentHierarchy.Level.CurrentDateMember,
  [Measures].[Story Points remaining])
* DateDiffDays([Time].CurrentHierarchyMember.StartDate,
   [Measures].[Predicted completion date (Story Points) (Recent Performance)])
/ DateDiffDays(Now(),[Measures].[Predicted completion date (Story Points) (Recent Performance)])
WHEN
DateInPeriod(
  'Today',
  [Time].CurrentHierarchyMember
)
THEN
NonZero([Measures].[Story Points remaining])
END
  1. When I filter to a specific legend, the Predicted Date disappears from "Predicted dates (Story Points) despite being properly calculated and appearing in “Predicted completion date (Story Points) (Recent Performance.” I would expect in the second report image to see “Sep 29 2022” in W40, how do I fix this?

  2. Why, after filtering to a specific Legend, did the report add 7 new periods of increasingly negative values for “Story Point Projection”? I would hope this is actually solved by whatever addresses question 1.

Thanks again!

  1. try this formula to stop the projection at 0
CASE WHEN
DateBetween(
    [Time].CurrentHierarchymember.StartDate,
    'Today',
    -- predicted date by rolling period
    [Measures].[Predicted completion date (Story Points) (Recent Performance)]
    )

THEN 
([Time].CurrentHierarchy.Level.CurrentDateMember,
  [Measures].[Story Points remaining])
* DateDiffDays([Time].CurrentHierarchyMember.StartDate,
   [Measures].[Predicted completion date (Story Points) (Recent Performance)])
/ DateDiffDays(Now(),[Measures].[Predicted completion date (Story Points) (Recent Performance)])
WHEN
DateInPeriod(
  'Today',
  [Time].CurrentHierarchyMember
)
THEN
NonZero([Measures].[Story Points remaining])
END
  1. What formula to you use for “Predicted ates (Story points)”? It must be linked to the legend filter but you would need to make it ignore the legend selection.
  2. try fixing the step1 first as both questions are related

Martins

@martins.vanags can you clarify what you mean by “it must be linked to the legend filter but you would need to make it ignore the legend selection”? I’m not sure that I follow.

Here is my formulate for Predicated Dates (Story points):

--annotations.group=Predicted
Case When
  DateInPeriod(
    Now(),
    [Time].CurrentHierarchyMember)
Then
  'Today - ' 
  || + Format(Now(), 'Medium Date')
When
      DateInPeriod(
        [Fix Version].CurrentHierarchymember.Get('Release date'),
        [Time].CurrentHierarchyMember)  
Then
   'Milestone Lock - ' 
  || + Format([Fix Version].CurrentHierarchymember.Get('Release date'), 'Medium Date')
When 
  DateInPeriod(
    [Measures].[Predicted completion date (Story Points) (Recent Performance)],
    [Time].CurrentHierarchyMember)  
Then
  Case when
      DateInPeriod(
        [Fix Version].CurrentHierarchymember.Get('Release date'),
        [Time].CurrentHierarchyMember)
     Then
       'Predicted/Version date - ' 
      || Format([Measures].[Predicted completion date (Story Points) (Recent Performance)], 'Medium Date')
    Else
  'Predicted date - ' 
  || Format([Measures].[Predicted completion date (Story Points) (Recent Performance)], 'Medium Date')
  End
End

Thanks,
Ryan

@rbelmont
As you can see your calculated measure depends on another meausre “[Measures].[Predicted completion date (Story Points) (Recent Performance)]” in the formula.
Can you send me the formula for “[Measures].[Predicted completion date (Story Points) (Recent Performance)]”?

Martins

@rbelmont if you got this report work, do you mind sharing the report definition along with some explanation for the report so that I could use it for my reports? Thank you in advance.