🍻🍂Apptoberfest🍂🍻: Agile Reporting with eazyBI - Part 2 - Velocity Variance and Story Size Report

[#1]

Overview
In this video you can find a complete walkthrough on how you can create the Velocity Variance and Story Size Charts with EazyBI:  Video Tutorial Link 

What is Velocity Variance?

What is Story Size?

Export Definition

If you are an existing eazyBI user feel free to use the export definition below and import it to your eazyBI instance. 

Velocity Variance:

 {
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Velocity Variance”,
“folder_name”: “Agile Reports”,
“result_view”: “line_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Velocity Variance]","[Measures].[Upper Bound]","[Measures].[Lower Bound]"],“members”:null}]},“rows”:{“dimensions”:[{“name”:“Sprint”,“selected_set”:["[Sprint].[Sprint].Members"],“members”:null,“bookmarked_members”:null}],“filter_by”:{“conditions”:[{“expression”:["[Measures].[Sprint Story Points committed]"],“operator”:"\u003e",“value”:“0”}]}},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].[All Projects]"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Project Marvel “,“full_name”:”[Project].[Project Marvel ]”,“drillable”:true,“key”:“MARVEL”,“parent_full_name”:"[Project].[All Projects]"}],“bookmarked_members”:null,“current_page_members”:["[Project].[Project Marvel ]"]}]},“options”:{},“view”:{“current”:“line_chart”,“maximized”:false,“line_chart”:{“area”:false,“swap_axes”:false,“data_labels”:true,“series_options”:{“Upper Bound”:{“type”:“area”,“color”:"#CAF8FF",“dataLabelType”:false},“Lower Bound”:{“type”:“area”,“color”:"#CAF8FF",“dataLabelType”:false},“Velocity Variance”:{“type”:“spline”}},“x_axis”:{“0”:{“title”:“Sprint Name”}},“y_axis”:{“0”:{“title”:“Percentage”}}},“table”:{}},“calculated_members”:null}
} ],
“calculated_members”: [{“name”:“Sprint Story Points committed”,“dimension”:“Measures”,“format_string”:"#,##0.00",“formula”:"( [Measures].[Story Points added],\n [Transition Field].[Sprint status],\n [Sprint Status].[Active],\n – An issue was in a sprint at a sprint start time\n [Issue Sprint Status Change].[Future =\u003e Active]\n)\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 Variance”,“dimension”:“Measures”,“formula”:"-- Velocity Variance = ([5 Sprint Rolling Avg. Velocity – Current Velocity])/ 5 Sprint Rolling Avg. Velocity\n([Measures].[Running Story Points velocity for 5 closed sprints]-[Measures].[Sprint Story Points completed])\n/[Measures].[Running Story Points velocity for 5 closed sprints]",“format_string”:"#0.00%"},{“name”:“Upper Bound”,“dimension”:“Measures”,“formula”:“0.10”,“format_string”:"#0.00%"},{“name”:“Lower Bound”,“dimension”:“Measures”,“formula”:"-0.10",“format_string”:"#0.00%"}]
}

Story Size:

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Story Size”,
“folder_name”: “Agile Reports”,
“result_view”: “bar_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Story Size]"],“members”:null}]},“rows”:{“dimensions”:[{“name”:“Sprint”,“selected_set”:["[Sprint].[Sprint].Members"],“members”:null,“bookmarked_members”:null}],“filter_by”:{“conditions”:[{“expression”:["[Measures].[Sprint Story Points committed]"],“operator”:"\u003e",“value”:“0”}]}},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].[All Projects]"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Project Marvel “,“full_name”:”[Project].[Project Marvel ]”,“drillable”:true,“key”:“MARVEL”,“parent_full_name”:"[Project].[All Projects]"}],“bookmarked_members”:null,“current_page_members”:["[Project].[Project Marvel ]"]}]},“options”:{},“view”:{“current”:“bar_chart”,“maximized”:false,“bar_chart”:{“stacked”:false,“vertical”:true,“swap_axes”:false,“data_labels”:true,“series_options”:{},“x_axis”:{“0”:{“title”:“Sprint Name”}},“y_axis”:{“0”:{“title”:“Story Points”}}},“table”:{}},“calculated_members”:null}
} ],
“calculated_members”: [{“name”:“Sprint Story Points committed”,“dimension”:“Measures”,“format_string”:"#,##0.00",“formula”:"( [Measures].[Story Points added],\n [Transition Field].[Sprint status],\n [Sprint Status].[Active],\n – An issue was in a sprint at a sprint start time\n [Issue Sprint Status Change].[Future =\u003e Active]\n)\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”:“Sprint issues at closing”,“dimension”:“Measures”,“format_string”:"#,##0",“formula”:"( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Closed],\n – An issue was in a sprint at closing\n [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{“name”:“Sprint issues completed”,“dimension”:“Measures”,“format_string”:"#,##0",“formula”:"(\n [Measures].[Sprint issues at closing],\n [Transition Status.Category].[Done]\n)\n"},{“name”:“Story Size”,“dimension”:“Measures”,“formula”:"-- Sprint Story points completed/Sprint Issues Completed\nCASE WHEN IsEmpty([Measures].[Sprint issues completed]) THEN\n 0\nELSE\n [Measures].[Sprint Story Points completed]/\n [Measures].[Sprint issues completed]\nEND",“format_string”:"#,##0.00"}]
}