Hello, I need your assistance with an issue. Despite trying to adapt many pre-made scripts like Rolling Average, I couldn’t achieve the desired result. Here’s what I need:
We need a reference velocity average for our teams, which will allow filtering based on project or board dimensions. For example, I want to calculate the average velocity of all completed sprints in 2024 for Board A. This value will serve as a benchmark for comparison for teams running sprints in 2025, both at the sprint and board levels.
For instance, if the Velocity Reference value is 50, the team can calculate the Targeted Value Contribution Index at the sprint level using the formula: (current velocity - reference velocity) / reference velocity
to see the performance trend.
Similarly, if the team wants to view the metric at the board level, it should calculate the average velocity of all 2025 sprints for that board and apply the same formula: (average 2025 velocity - reference velocity) / reference velocity.
I need this setup to function properly and display trends at both sprint and board levels. Could you help me achieve this?
To calculate the average velocity of all completed sprints in 2024, there is no need to roll up. Please use the DateInPeriod and take the Year from Time dimension.
For example:
Avg(
Filter(
DescendantsSet([Sprint].CurrentHierarchyMember,[Sprint].CurrentHierarchy.Levels("Sprint")),
-- only sprints with committed story points are retrieved
[Sprint].CurrentHierarchyMember.getBoolean('Closed') AND
DateInPeriod(
[Sprint].CurrentHierarchyMember.get('Complete date'),
[Time].[2024]) AND
([Measures].[Sprint Story Points completed],[Time].[2024]) > 0
),
[Measures].[Sprint Story Points completed]
)
Current velocity - completed in a single sprint would be measured - “Sprint Story Points completed” from Sprint Scoupe measures: Jira Software custom fields
thank you, @ilze.mezite
if the sprint end is 2023 or 2025 the measure does not work. But I am just trying to define avg velocity 2024 and benchmark this value to our current velocity to monitoring value increase.
that’s my report file.
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Sample Score Card Calculations",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Velocity]","[Measures].[Base Velocity (2024)]","[Measures].[Targeted Value Increase %]","[Measures].[Sprint end date]","[Measures].[Targeted Value Contribution Increase Indicator]","[Measures].[Multiple sprints velocity by boards]","[Measures].[Story Points velocity for 5 closed sprints]","[Measures].[Running Story Points velocity for 5 closed sprints]","[Measures].[Last closed sprints by boards]","[Measures].[Velocity (2024)]"],"members":[]}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[Darwin Freya Rapid Board]"],"members":[{"depth":1,"name":"Darwin Freya Rapid Board","full_name":"[Sprint].[Darwin Freya Rapid Board]","drillable":true,"drilled_into":false,"expanded":true}],"bookmarked_members":[]}]},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Project].[All Projects]"]},{"name":"Sprint","duplicate":true,"selected_set":["[Sprint].[All Sprints]"],"members":[{"depth":0,"name":"All Sprints","full_name":"[Sprint].[All Sprints]","drillable":true,"type":"all","expanded":true},{"depth":1,"name":"Darwin Freya Rapid Board","full_name":"[Sprint].[Darwin Freya Rapid Board]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Sprint].[All Sprints]"}],"bookmarked_members":[],"current_page_members":["[Sprint].[Darwin Freya Rapid Board]"]}],"nonempty_crossjoin":false},"options":{},"view":{"current":"table","maximized":false,"table":{}},"calculated_members":[]}
} ],
"calculated_members": [{"name":"Story Points velocity for 5 closed sprints","dimension":"Measures","formula":"-- annotations.group=Agile Calculations\n-- average velocity from last 5 closed sprints \nAvg(\n Tail(Order(\n -- filter last 5 closed sprints of any sprint selection or from all sprints\n Filter(\n DescendantsSet([Sprint].CurrentHierarchyMember,[Sprint].CurrentHierarchy.Levels(\"Sprint\")),\n -- only sprints with committed story points are retrieved\n [Sprint].CurrentHierarchyMember.getBoolean('Closed') AND\n NOT IsEmpty([Sprint].CurrentHierarchyMember.get('Complete date')) AND\n ([Measures].[Sprint Story Points completed],[Time].CurrentHierarchy.DefaultMember) \u003e 0\n ),\n -- order by completion date\n [Sprint].CurrentHierarchyMember.get('Complete date'), BASC ) ,\n -- last 5 sprints\n 5 ), \n [Measures].[Sprint Story Points completed]\n)","format_string":""},{"name":"Sprint end date","dimension":"Measures","format_string":"mmm dd yyyy","formula":"[Sprint].CurrentHierarchyMember.get('End date')"},{"name":"Last closed sprints by boards","dimension":"Measures","format_string":"","formula":"CASE\nWHEN\n ([Measures].[Issues created],\n [Sprint].CurrentHierarchyMember.Parent) \u003e 0\nTHEN\n NonZero(\n Rank(\n [Sprint].CurrentHierarchyMember,\n Order(\n Filter(\n [Sprint].CurrentHierarchyMember.Parent.Children,\n NOT isEmpty([Sprint].CurrentHierarchyMember.Get('Start date'))\n AND [Sprint].CurrentHierarchyMember.GetBoolean('Closed')\n ),\n [Sprint].CurrentHierarchyMember.Get('Start date'), DESC)\n )\n )\nEND\n"},{"name":"Sprint Story Points at closing","dimension":"Measures","format_string":"#,##0.00","formula":"-- Story points from issues when a sprint was closed\n( [Measures].[Story Points added],\n [Transition Field].[Sprint status],\n [Sprint Status].[Closed],\n [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint Story Points completed","dimension":"Measures","format_string":"#,##0.00","formula":"(\n [Measures].[Sprint Story Points at closing],\n [Transition Status.Category].[Done]\n)\n"},{"name":"Running Story Points velocity for 5 closed sprints","dimension":"Measures","format_string":"#,##0.00","formula":"CASE\n WHEN\n [Sprint].CurrentHierarchyMember.Level.Name = 'Sprint' AND\n [Sprint].CurrentHierarchyMember.GetBoolean('Closed')\n THEN\n AVG(\n Tail(\n Filter(\n -- filter last 5 previous sprints in a board starting from current sprint\n [Sprint].CurrentHierarchyMember.FirstSibling:\n [Sprint].CurrentHierarchyMember,\n [Sprint].CurrentHierarchyMember.GetBoolean('Closed')\n ),\n -- set the count of last closed sprints for running velocity\n 5 ) ,\n [Measures].[Sprint Story Points completed]\n )\nEND\n"},{"name":"Velocity","dimension":"Measures","formula":"-- annotations.group=0.Agile Metrics\n-- How many Story Points can the Team commit to and achieve by the end of a Sprint?\n\n[Measures].[Sprint Story Points completed]\n","format_string":"#,##0.00"},{"name":"Targeted Value Increase %","dimension":"Measures","formula":"-- annotations.group=0.Agile Metrics\n-- How many more Story Points is the Team able to successfully handle now, compared to their very first Sprint together?\n-- (Current Sprint's Velocity - Original Velocity ) ÷ Original Velocity\n\n([Measures].[Velocity] - [Measures].[Base Velocity (2024)]) /\n[Measures].[Base Velocity (2024)]\n","format_string":"#0.00%"},{"name":"Base Velocity (2024)","dimension":"Measures","formula":"-- annotations.group=0.Agile Metrics\nCASE \n -- Eğer Sprint seviyesindeysen\n WHEN [Sprint].CurrentHierarchyMember.Level.Name = \"Sprint\" THEN\n Avg(\n Tail(\n Order(\n Filter(\n Descendants([Sprint].CurrentHierarchyMember.Parent, [Sprint].[Sprint]),\n [Sprint].CurrentHierarchyMember.GetBoolean('Closed') AND\n Year(DateParse([Sprint].CurrentHierarchyMember.Get('End date'))) = 2024\n ),\n [Sprint].CurrentHierarchyMember.Get('Complete date'), BASC\n ),\n 5\n ),\n [Measures].[Sprint Story Points completed]\n )\n -- Eğer Board seviyesindeysen\n WHEN [Sprint].CurrentHierarchyMember.Level.Name = \"Board\" THEN\n Avg(\n Tail(\n Order(\n Filter(\n Descendants([Sprint].CurrentHierarchyMember, [Sprint].[Sprint]),\n [Sprint].CurrentHierarchyMember.GetBoolean('Closed') AND\n Year(DateParse([Sprint].CurrentHierarchyMember.Get('End date'))) = 2024\n ),\n [Sprint].CurrentHierarchyMember.Get('Complete date'), BASC\n ),\n 5\n ),\n [Measures].[Sprint Story Points completed]\n )\n -- Diğer durumlar için (örneğin proje seviyesi)\n ELSE\n Avg(\n Tail(\n Order(\n Filter(\n Descendants([Sprint].CurrentHierarchy.DefaultMember, [Sprint].[Sprint]),\n [Sprint].CurrentHierarchyMember.GetBoolean('Closed') AND\n Year(DateParse([Sprint].CurrentHierarchyMember.Get('End date'))) = 2024\n ),\n [Sprint].CurrentHierarchyMember.Get('Complete date'), BASC\n ),\n 5\n ),\n [Measures].[Sprint Story Points completed]\n )\nEND\n","format_string":"#,##0.00"},{"name":"Targeted Value Contribution Increase Indicator","dimension":"Measures","formula":"-- annotations.group=Indicator and Threshold \n\n\nCASE\n WHEN [Measures].[Targeted Value Increase %] \u003e= 0.5 THEN\n \"\u003cspan style='color:blue; font-size:16px;'\u003e\u0026#x1F535;\u003c/span\u003e Hyper-Productive (50% and above)\" -- Blue Indicator\n WHEN [Measures].[Targeted Value Increase %] \u003e= 0.2 THEN\n \"\u003cspan style='color:green; font-size:16px;'\u003e\u0026#x1F7E2;\u003c/span\u003e Good Performance (20% to 50%)\" -- Green Indicator\n ELSE\n \"\u003cspan style='color:red; font-size:16px;'\u003e\u0026#x1F534;\u003c/span\u003e Below Target (Under 20%)\" -- Red Indicator\nEND\n","format_string":"HTMLFormatter"},{"name":"Multiple sprints velocity by boards","dimension":"Measures","formula":"-- annotations.group=Agile Calculations\n-- formula will work only for default hierarchy with Board level as expected\nSum(\n -- get boards\n Generate( \n { [Sprint].CurrentMember,\n ChildrenSet([Sprint].CurrentMember) },\n Ancestor([Sprint].CurrentMember,[Sprint].[Board])\n ),\n Avg(Tail( --averga within a board\n Filter(\n [Sprint].CurrentMember.Children,\n [Sprint].CurrentMember.GetBoolean(\"Closed\") \n ),\n 5) , -- last 5 closed boards \n [Measures].[Sprint Story Points completed]\n )\n)","format_string":"#,##0.00"},{"name":"Velocity (2024)","dimension":"Measures","formula":"Avg(\n Filter(\n DescendantsSet([Sprint].CurrentHierarchyMember,[Sprint].CurrentHierarchy.Levels(\"Sprint\")),\n -- only sprints with committed story points are retrieved\n [Sprint].CurrentHierarchyMember.getBoolean('Closed') AND\n DateInPeriod(\n [Sprint].CurrentHierarchyMember.get('Complete date'),\n [Time].[2024]) AND\n ([Measures].[Sprint Story Points completed],[Time].[2024]) \u003e 0\n ),\n [Measures].[Sprint Story Points completed]\n)","format_string":""}]
}