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"}]
}
