Get max and min from last 5 sprints velocities

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”:“”}]
}

Hi @arilai,

I suggest adding the Sprint dimension to the report pages if you have multiple boards with significant differences in the scope or teams because sprints in each board are considered separately.

The expression for the “Optimistic velocity slope” might look as follows.

``````CASE WHEN
[Sprint].CurrentMember.Level.Name = "Sprint"
THEN
Max(
Tail(
Filter(
-- filter last 5 closed sprints in a board of current sprint
[Sprint].CurrentMember.Siblings,
[Sprint].CurrentMember.GetBoolean("Closed")
),
-- set the count of last closed sprints
5),
[Measures].[Sprint Story Points completed]
)
--if there is board on page selection or nothing selected in page filters
ELSE
Max(
Tail(
Order(
Filter(
-- filter last 5 closed sprints in selected or any board
Descendants([Sprint].CurrentMember, [Sprint].[Sprint]),
[Sprint].CurrentMember.GetBoolean("Closed")
),
DateToTimestamp([Sprint].CurrentMember.Get('End date')),
BASC),
-- set the count of last closed sprints
5),
[Measures].[Sprint Story Points completed])
END
``````

The pessimistic slope would have MIN instead of MAX.

``````CASE WHEN
[Sprint].CurrentMember.Level.Name = "Sprint"
THEN
Min(
Tail(
Filter(
-- filter last 5 closed sprints in a board of current sprint
[Sprint].CurrentMember.Siblings,
[Sprint].CurrentMember.GetBoolean("Closed")
),
-- set the count of last closed sprints
5),
[Measures].[Sprint Story Points completed]
)
--if there is board on page selection or nothing selected in page filters
ELSE
Min(
Tail(
Order(
Filter(
-- filter last 5 closed sprints in selected or any board
Descendants([Sprint].CurrentMember, [Sprint].[Sprint]),
[Sprint].CurrentMember.GetBoolean("Closed")
),
DateToTimestamp([Sprint].CurrentMember.Get('End date')),
BASC),
-- set the count of last closed sprints
5),
[Measures].[Sprint Story Points completed])
END
``````

Regards,
Oskars / support@eazyBI.com