Hello, I hope you can help me with this.
We calculate rolling averages of different metrics over the past 5 sprints for our teams. Recently we noticed that if a team were to change boards but continue their sprint cadence the calculation for the rolling sprints does not translate across the boards, as sprints appear to be a aligned to the board.
To illustrate, I have created a measure to get the last 5 sprints (as a string) for each closed sprint as follows:
CASE
WHEN
[Sprint].CurrentHierarchyMember.Level.Name = 'Sprint'
AND [Sprint].CurrentHierarchyMember.GetBoolean('Closed')
THEN
SetToStr(
Tail(
order(
Filter(
-- filter last 5 previous sprints in a board starting from current sprint
[Sprint].CurrentHierarchyMember.FirstSibling:
[Sprint].CurrentHierarchyMember,
[Sprint].CurrentHierarchyMember.GetBoolean('Closed')
and [Measures].[Sprint Story Points at closing] > 0
),[Sprint].CurrentMember.get('Start date'),basc)
,
-- set the count of last closed sprints for running velocity
5 )
)
END
The report returns a list of all closed sprints for the “Power Rangers” team over the last 3 months and it was during sprint 26 where the team changed boards.
How can I adjust the measure so that sprint 26 and 27 contain the running list of sprints associated to the team, regardless of the board?
Expected result for Sprint 26 running string:
{[Sprint].[Consultant Digital Portal].[CDP Sprint 22],
[Sprint].[Consultant Digital Portal].[CDP Sprint 23],
[Sprint].[Consultant Digital Portal].[CDP Sprint 24],
[Sprint].[Consultant Digital Portal].[CDP Sprint 25],
[Sprint].[RS Power Rangers].[CDP Sprint 26]
Thanks in advance for any help you can provide!
@daina.tupule @martins.vanags @janis.plume @gerda.grantina
Any assistance on this would be greatly appreciated! Although not a common scenario, we’ve seen this occur and the metrics are disrupted. It seems like something simple enough to evaluate with correct logic, we just can’t figure it out. Thanks again!
Hi,
That seems to be quite a tricky solution to get the latest five closed sprints from any board before the current sprint. The following solution generates the set using the other hierarchy of the Sprint dimension. You should check that your version of eazyBI has this hierarchy:
The formula for getting the set is the following:
SetToStr(
Tail (
Head(
Order(
Filter(
[Sprint.By status].[Sprint].Members,
[Sprint.By status].CurrentHierarchyMember.GetBoolean('Closed')
),
[Sprint.By status].CurrentHierarchyMember.GetDate('Start date'),
BASC
),
Rank([Sprint].CurrentMember,
Order(Filter(
[Sprint].[Sprint].Members,
[Sprint].CurrentHierarchyMember.GetBoolean('Closed')
),
[Sprint].CurrentHierarchyMember.GetDate('Start date'),
BASC
)
)-1
),
6)
)
This code generates the set of sprints from another hierarchy, but measures should work the same way for the sprints from this hierarchy.
Kindly,
Janis eazyBI support
@janis.plume thanks for responding. That solution does not seem to do the job. It appears it does not consider the page level filter for “Team” when identifying the last 5 sprints. Instead it pulls back a set of sprints across all teams.
The report uses a page level filter from the Sprint dimension, Last 3 Months, which is a calculated member that returns all closed sprints within the last 3 months as follows:
Aggregate(
Order(
Filter([Sprint].[Sprint].Members,
[Sprint].CurrentMember.getBoolean('Closed') AND
NOT IsEmpty([Sprint].CurrentMember.get('Complete date')) AND
DateBetween([Sprint].CurrentMember.get('Start date'),
'3 month ago',
'tomorrow') ),
[Sprint].CurrentMember.get('Start date'),
BASC
)
)
This combined with the Team page filter returns a list of closed sprints for the selected team that has issues created > 0 (report filter). The end result displays a list of sprints regardless of board that had assigned issues to the team.
Essentially I need this calculated measure to do the same.
Any ideas?
Hi,
Sorry for the delay.
We can extend the filtering condition in my previous formula by applying the “report context”. That means using at least one measure in the condition.
Please, check the following code, which filters only sprints that are related to the team:
SetToStr(
Tail (
Head(
Order(
Filter(
[Sprint.By status].[Sprint].Members,
[Sprint.By status].CurrentHierarchyMember.GetBoolean('Closed')
AND ([Measures].[Issues created],[Sprint].CurrentHierarchy.DefaultMember)>0
),
[Sprint.By status].CurrentHierarchyMember.GetDate('Start date'),
BASC
),
Rank([Sprint].CurrentMember,
Order(Filter(
[Sprint].[Sprint].Members,
[Sprint].CurrentHierarchyMember.GetBoolean('Closed')
),
[Sprint].CurrentHierarchyMember.GetDate('Start date'),
BASC
)
)
),
5)
)
Kindly,
Janis, eazyBI support
@janis.plume You’re getting closer, but it’s missing the “rolling sprint” aspect. It appropriately returns the last 5 sprints for the team (across boards), but not from the point in time of the sprint. It’s just the last 5 sprints from the end and shows the same results for every record.
Here is a screen shot of the results from your solution:
I want to capture the rolling sprints as follows:
. . .
Sprint 26: 22, 23, 24, 25, 26
Sprint 27: 23, 24, 25, 26, 27
Sprint 28: 24, 25, 26, 27, 28
Hi Drew,
Thank you for rechecking the solution. There was, indeed, a shortage in my previous formula. The problem was, that the code did not correctly consider the page filtering, but I cannot confirm that it is not assuming the “rolling sprint” requirement.
Here is the slightly updated code I use now:
SetToStr(
Tail (
Head(
Order(
Filter(
[Sprint.By status].[Sprint].Members,
[Sprint.By status].CurrentHierarchyMember.GetBoolean('Closed')
AND ([Measures].[Issues created],[Sprint].CurrentHierarchy.DefaultMember)>0
),
[Sprint.By status].CurrentHierarchyMember.GetDate('Start date'),
BASC
),
Rank([Sprint].CurrentMember,
Order(Filter(
[Sprint].[Sprint].Members,
[Sprint].CurrentHierarchyMember.GetBoolean('Closed')
AND
[Measures].[Issues created]>0
),
[Sprint].CurrentHierarchyMember.GetDate('Start date'),
BASC
)
)
),
5)
)
Here is my test report for checking:
Kindly,
Janis, eazyBI support
You nailed it @janis.plume !
Now, the assumption I made was that once I was able to correctly identify the correct collection of sprints that I could simply wrap that with an average function to calculate the rolling average velocity for the last 5.
I either assumed wrong, or I messed something up as I’m not getting the correct results there. Instead of an average of the story points completed in those 5 sprints, it is just displaying the completed story points for that sprint record.
Do you see my mistake?
AVG(
Tail (
Head(
Order(
Filter(
[Sprint.By status].[Sprint].Members,
[Sprint.By status].CurrentHierarchyMember.GetBoolean('Closed')
AND ([Measures].[Issues created],[Sprint].CurrentHierarchy.DefaultMember)>0
),
[Sprint.By status].CurrentHierarchyMember.GetDate('Start date'),
BASC
),
Rank([Sprint].CurrentMember,
Order(Filter(
[Sprint].[Sprint].Members,
[Sprint].CurrentHierarchyMember.GetBoolean('Closed')
AND
[Measures].[Issues created]>0
),
[Sprint].CurrentHierarchyMember.GetDate('Start date'),
BASC
)
)
),
5),
[Measures].[Sprint Story Points completed]
)
Hi, Drew,
Sorry for not responding sooner (vacation season is here).
There is one more trick for the calculation of the average. As your report contains the Sprint dimension in the report rows, the completed story points in the formula use the sprint from the rows as the context for the measure. We must default the Sprint dimension from the report rows, so the calculation is limited to the set of sprints generated in our formula:
Avg(
Tail (
Head(
Order(
Filter(
[Sprint.By status].[Sprint].Members,
[Sprint.By status].CurrentHierarchyMember.GetBoolean('Closed')
AND ([Measures].[Issues created],[Sprint].CurrentHierarchy.DefaultMember)>0
),
[Sprint.By status].CurrentHierarchyMember.GetDate('Start date'),
BASC
),
Rank([Sprint].CurrentMember,
Order(Filter(
[Sprint].[Sprint].Members,
[Sprint].CurrentHierarchyMember.GetBoolean('Closed')
AND
[Measures].[Issues created]>0
),
[Sprint].CurrentHierarchyMember.GetDate('Start date'),
BASC
)
)
),
5),
([Measures].[Sprint Story Points completed],
[Sprint].DefaultMember)
)
Best regard,
Janis, eazyBI support
