Thanks again!
It’s very close. Only thing is I am still seeing all of the sprints for a particular board when I expand, rather than just the sprints that were closed in the previous month. (see below)
Hi @cpuglisi
This is when you should use the filter on the measures. Now it should display only the needed sprints.
Martins
Thanks @martins.vanags
Looks like it’s working well. Only thing is it doesn’t seem to be summing the individual rows properly (at the dropdown level)
Also, is it possible to get the average of a column and have that displayed at the topmost row for each board? (see illustration)
Hi @cpuglisi
Please export and share the definition of your report
In most cases, such calculated measures like “Velocity” in your case, would ignore the filter conditions when calculating results on aggregated level.
Martins / eazyBI
Hi @martins.vanags,
Below is the report definition:
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Monthly Agile Team Metrics",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Issues created]","[Measures].[Velocity]","[Measures].[Sustained Velocity]","[Measures].[Team Alignment]","[Measures].[Shifting Priorities]","[Measures].[Sprints Completed Last Month]"],"members":[]}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[Active Scrum Teams]"],"members":[{"depth":0,"name":"Active Scrum Teams","full_name":"[Sprint].[Active Scrum Teams]","calculated":true,"drillable":true,"expanded":true,"drilled_into":false},{"depth":1,"name":"FMC Screeners Scrum Board","full_name":"[Sprint].[FMC Screeners Scrum Board]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Sprint].[Active Scrum Teams]"},{"depth":1,"name":"MCX Agile Team 1 Scrum Board","full_name":"[Sprint].[MCX Agile Team 1 Scrum Board]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Sprint].[Active Scrum Teams]"}],"bookmarked_members":[]}],"filter_by":{"conditions":[{"expression":["[Measures].[Sprints Completed Last Month]"],"operator":"between","value":"1,4"}]}},"pages":{"dimensions":[]},"options":{},"view":{"current":"table","maximized":false,"table":{}}}
} ],
"calculated_members": [{"name":"Sprint complete date","dimension":"Measures","format_string":"mmm dd yyyy","annotations":{"group":"Agile","predefined":true},"formula":"[Sprint].CurrentMember.get('Complete date')"},{"name":"Sprint closed?","dimension":"Measures","format_string":"","annotations":{"group":"Agile","predefined":true},"formula":"CASE [Sprint].CurrentMember.getBoolean('Closed')\nWHEN CBool(1) THEN 'Yes'\nWHEN CBool(0) THEN 'No'\nEND"},{"name":"All closed sprints","dimension":"Sprint","format_string":"","formula":"Aggregate(\n Order(\n Filter([Sprint].[Sprint].Members,\n [Sprint].CurrentMember.getBoolean('Closed') AND\n NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))),\n [Sprint].CurrentMember.get('Start date'),\n BASC\n )\n)","annotations":{"predefined":true}},{"name":"Sprint issues added","dimension":"Measures","format_string":"#,##0","annotations":{"group":"Agile","predefined":true},"formula":"( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Active],\n -- An issue was added or created in an active sprint\n [Issue Sprint Status Change].[(none) =\u003e Active]\n)"},{"name":"Sprint issues at closing","dimension":"Measures","format_string":"#,##0","annotations":{"group":"Agile","predefined":true},"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)"},{"name":"Sprint issues completed","dimension":"Measures","format_string":"#,##0","annotations":{"group":"Agile","predefined":true},"formula":"(\n [Measures].[Sprint issues at closing],\n [Transition Status.Category].[Done]\n)"},{"name":"Velocity","dimension":"Measures","formula":"[Measures].[Sprint issues completed]\n\n","format_string":"#,##0"},{"name":"Sustained Velocity","dimension":"Measures","formula":"CASE WHEN\n [Sprint].CurrentMember is [Sprint].DefaultMember\n OR\n -- for closed sprints only\n [Sprint].CurrentMember.getBoolean('Closed') AND\n NOT IsEmpty([Sprint].CurrentMember.get('Complete date')) AND\n [Measures].[Sprint issues completed] \u003e 0\nTHEN\n AVG(\n Tail(\n -- filter last 5 closed sprints starting from current sprint\n Filter(\n Head(\n Cache(ChildrenSet([Sprint].[All closed sprints])),\n Rank([Sprint].CurrentMember,\n Cache(ChildrenSet([Sprint].[All closed sprints]))\n )\n ),\n -- only sprints with committed story points are retrieved\n [Measures].[Sprint Issues Completed] \u003e 0\n ), 3\n ),\n [Measures].[Sprint Issues completed]\n )\nEND","format_string":""},{"name":"Team Alignment","dimension":"Measures","formula":"([Measures].[Sprint issues added],\n[Issue Type].[Bug])","format_string":"#,##0"},{"name":"Shifting Priorities","dimension":"Measures","formula":"CASE WHEN\n [Sprint].CurrentMember is [Sprint].DefaultMember\n OR\n -- for closed sprints only\n [Sprint].CurrentMember.getBoolean('Closed') AND\n NOT IsEmpty([Sprint].CurrentMember.get('Complete date')) AND\n [Measures].[Sprint issues completed] \u003e 0\nTHEN\n AVG(\n Tail(\n -- filter last 5 closed sprints starting from current sprint\n Filter(\n Head(\n Cache(ChildrenSet([Sprint].[All closed sprints])),\n Rank([Sprint].CurrentMember,\n Cache(ChildrenSet([Sprint].[All closed sprints]))\n )\n ),\n -- only sprints with committed story points are retrieved\n [Measures].[Sprint Issues Completed] \u003e 0\n ),1\n ),\n CASE WHEN\n [Sprint].CurrentMember is [Sprint].DefaultMember\n OR\n -- for closed sprints only\n [Sprint].CurrentMember.getBoolean('Closed') AND\n NOT IsEmpty([Sprint].CurrentMember.get('Complete date')) AND\n [Measures].[Sprint issues completed] \u003e 0\nTHEN\n AVG(\n Tail(\n -- filter last 5 closed sprints starting from current sprint\n Filter(\n Head(\n Cache(ChildrenSet([Sprint].[All closed sprints])),\n Rank([Sprint].CurrentMember,\n Cache(ChildrenSet([Sprint].[All closed sprints]))\n )\n ),\n -- only sprints with committed story points are retrieved\n [Measures].[Sprint Issues Completed] \u003e 0\n ), 3\n ),\n ([Measures].[Sprint issues added] /\n [Measures].[Sprint issues at closing])\n )\nEND\n )\nEND\n\n\n\n\n\n\n\n[Measures].[Sprint issues added] /\n[Measures].[Sprint issues at closing]","format_string":"#0%"},{"name":"Active Scrum Teams","dimension":"Sprint","formula":"Aggregate({\n[Sprint].[FMC Screeners Scrum Board],\n[Sprint].[FMC Agile Team Scrum Board],\n[Sprint].[MCX Agile Team 1 Scrum Board],\n[Sprint].[MCX Agile Team 1 Scrum Board]\n})\n\n\n\n\n\n\n\n","format_string":""},{"name":"Sprints Completed Last Month","dimension":"Measures","formula":"NonZero(\nCount(\nFilter(\nDescendants([Sprint].CurrentHierarchyMember,[Sprint].[Sprint]),\n[Measures].[Sprint closed?]=\"Yes\"\nAND\nDateInPeriod(\n [Measures].[Sprint complete date],\n [Time].[Month].CurrentdateMember.prevmember\n)\nAND\n[Measures].[Issues created]\u003e0\n)\n)\n)","format_string":"#,##0"}]
}
Thanks!
Hi,
Thanks,
In this case, you should incorporate the same logic (which you use for filter in toolbars) in each fo these calculated measures as well.
For example.
“Velocity” measure currently is a simpole formula “[Measures].[Sprint issues completed]” which would add all completed issues for all sprints from the board. And then you just hide sprints that were not completed last month.
To re-calculate velocity for only last month months sprints, you would need to use a different formula:
NonZero(
Sum(
Filter(
Descendants([Sprint].CurrentHierarchyMember,[Sprint].[Sprint]),
[Measures].[Sprint closed?]="Yes"
AND
DateInPeriod(
[Measures].[Sprint complete date],
[Time].[Month].CurrentdateMember.prevmember
)
AND
[Measures].[Issues created]>0
),
[Measures].[Sprint Issues completed]
)
)
Note the code similarity with your existing measure “Sprints completed last month”
Similarly, you would change the calculation also for other measures.
Best regards,
@martins.vanags Thank you! this worked perfectly. What if I wanted to change from seeing “prev month” to a specific month (ex. 'October", August") etc… and keep the rollup, etc the same?
Hi,
This particular calculation would always look at the previous month of selected month.
To use the selected month you would need to remove .Prevmember part from the code.
Martins / eazyBI support
Hi,
That code would always use the month which you select in “Time” dimension.
If you want to calculate the results for September, you can change the code for September only:
NonZero(
Sum(
Filter(
Descendants([Sprint].CurrentHierarchyMember,[Sprint].[Sprint]),
[Measures].[Sprint closed?]="Yes"
AND
DateInPeriod(
[Measures].[Sprint complete date],
[Time].[2019].[Q3 2019].[Sep 2019]
)
AND
[Measures].[Issues created]>0
),
[Measures].[Sprint Issues completed]
)
)
Martins / eazyBI support
Hi,
Any chance I can get some help with this one: Need set of sprints from specific gorup of boards
Thank you!!