Hi, community,
I want to get the (maximum and minimum) SP done in the last 5 sprints to use the values in some velocity slopes in a timeline chart.
Do you know how to relate those in a time (days) dimension?
For now, I’ve constants, but the objective is to have a constant based on the 5 last sprints closed.
I know it can be done by a MAX, SET, FILTER but I don’t accomplish the good definition.
Thanks very much!
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Burnup-chart-R1.1-Forecasting-Community”,
“result_view”: “line_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Today]”,“[Measures].[Optimistic velocity]”,“[Measures].[Pesimistic velocity]”,“[Measures].[Optimistic velocity slope]”,“[Measures].[Pesimistic velocity slope]”],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:[“[Time].[Releases interval]”],“members”:[{“depth”:0,“name”:“Releases interval”,“full_name”:“[Time].[Releases interval]”,“calculated”:true,“drillable”:true,“expanded”:true,“drilled_into”:false,“removed”:true}],“bookmarked_members”:[]}]},“pages”:{“dimensions”:[{“name”:“Issue Type”,“selected_set”:[“[Issue Type].[FE Issue Types]”],“members”:[{“depth”:0,“name”:“FE Issue Types”,“full_name”:“[Issue Type].[FE Issue Types]”,“calculated”:true,“drillable”:true,“dimension”:“Issue Type”,“formula”:“Aggregate(\n {[Issue Type].[Bug], \n [Issue Type].[Story],\n [Issue Type].[Improvement]}\n)”,“format_string”:“”,“annotations”:null,“dependent_calculated_members_count”:2,“dependent_report_ids”:[],“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Story”,“full_name”:“[Issue Type].[Story]”,“parent_full_name”:“[Issue Type].[FE Issue Types]”},{“depth”:1,“name”:“Improvement”,“full_name”:“[Issue Type].[Improvement]”,“parent_full_name”:“[Issue Type].[FE Issue Types]”}],“bookmarked_members”:[],“current_page_members”:[“[Issue Type].[Story]”,“[Issue Type].[Improvement]”]}]},“options”:{},“view”:{“current”:“line_chart”,“maximized”:false,“line_chart”:{“area”:false,“swap_axes”:false,“data_labels”:false,“series_options”:{“Cumulative Story Points resolved till today”:{“color”:“#86DBA6”,“type”:“area”},“Estimated release date”:{“type”:“plotline”,“dataLabelType”:“top vertical”},“Sprint value”:{“type”:“plotline”,“dataLabelType”:“top vertical”,“color”:“#EBECF0”},“Real story points resolved”:{“color”:“#CFF9DD”,“type”:“area”,“symbol”:false},“Story points to complete”:{“color”:“#2F579C”},“Issues with no story points”:{“type”:“text”},“Story Points history”:{“color”:“#7CA7E5”,“symbol”:false},“Predicted completion line”:{“color”:“#9043C2”},“All US”:{“color”:“#2F579C”},“(Estimated+Non-estimated) US”:{“color”:“#B3D4FF”,“symbol”:false,“type”:“area”},“Pesimistic velocity”:{“symbol”:false,“type”:“line-Dash”,“color”:“#F95E16”},“Optimistic velocity”:{“symbol”:false,“type”:“line-Dash”,“color”:“#006644”},“Pesimistic release date”:{“type”:“plotline”,“dataLabelType”:“top vertical”,“color”:“#F95E16”},“Optimistic release date”:{“type”:“plotline”,“color”:“#006644”,“dataLabelType”:“top vertical”},“Today”:{“type”:“plotline”,“dataLabelType”:“top vertical”}},“y_axis”:{“0”:{“max”:“600”,“min”:“300”}}},“table”:{“row_dimension_headers”:{“Time”:true}}},“calculated_members”:[]}
} ],
“calculated_members”: [{“name”:“Today”,“dimension”:“Measures”,“formula”:“CASE WHEN\nDateInPeriod(\n’Today’,\n[Time].CurrentHierarchyMember\n)\nThen\n 'Today - ’ \n || + Format(Now(), ‘Medium Date’)\n\nEnd”,“format_string”:“#,##0”},{“name”:“Real story points resolved”,“dimension”:“Measures”,“formula”:“Cache(\n Sum({PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember},\n [Measures].[Story Points resolved]\n )\n)”,“format_string”:“”},{“name”:“FE Issue Types”,“dimension”:“Issue Type”,“formula”:“Aggregate(\n {[Issue Type].[Bug], \n [Issue Type].[Story],\n [Issue Type].[Improvement]}\n)”,“format_string”:“”},{“name”:“Releases interval”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘02/29/2022’, ‘12/29/2022’)\n)”,“format_string”:“”},{“name”:“Optimistic velocity”,“dimension”:“Measures”,“formula”:“CASE WHEN – check that we are drawing the line starting from today and into future\nDateInPeriod(\n [Time].[Day].CurrentDateMember.StartDate,\n [Time].CurrentHierarchyMember)\n \n OR \n DateCompare(\n [Time].CurrentHierarchyMember.StartDate,\n now()\n ) \u003e= 0\n\nTHEN \n Val(([Measures].[Real story points resolved], [Time].[Day].CurrentDateMember)) – measure from which to start; in this case Open issues\n + [Measures].[Optimistic velocity slope]/14 * – coefficient you can change to change the slope\n DateDiffDays( – how far in future we are\n [Time].[Day].CurrentDateMember.StartDate,\n [Time].CurrentHierarchyMember.NextStartDate\n )\nEND”,“format_string”:“”},{“name”:“Optimistic velocity slope”,“dimension”:“Measures”,“formula”:“33\n– Here it should return the max value of SP in the last 5 sprints”,“format_string”:“#,##0.00”},{“name”:“Pesimistic velocity slope”,“dimension”:“Measures”,“formula”:“22.5\n– Here it should return the min value of SP in the last 5 sprints”,“format_string”:“#,##0.00”},{“name”:“Pesimistic velocity”,“dimension”:“Measures”,“formula”:“CASE WHEN – check that we are drawing the line starting from today and into future\nDateInPeriod(\n [Time].[Day].CurrentDateMember.StartDate,\n [Time].CurrentHierarchyMember)\n \n OR \n DateCompare(\n [Time].CurrentHierarchyMember.StartDate,\n now()\n ) \u003e= 0\n\nTHEN \n Val(([Measures].[Real story points resolved], [Time].[Day].CurrentDateMember)) – measure from which to start; in this case Open issues\n + [Measures].[Pesimistic velocity slope]/14 * – coefficient you can change to change the slope\n DateDiffDays( – how far in future we are\n [Time].[Day].CurrentDateMember.StartDate,\n [Time].CurrentHierarchyMember.NextStartDate\n )\nEND”,“format_string”:“”}]
}
