I am trying to create a report by sprint that shows the story points completed for each sprint along with average of the last 7 sprints (including that sprint). I can get to an average across the whole set but not to an average for only 7 of the sprints that changes for each sprint listed.
Attached pictures of what I was thinking along with color coded showing where the averages would come from.
Hi,
I`m no expert with eazyBi, but here is the query I use for rolling averages based on a certain date range.
Avg(
LastPeriods(7, [Time].CurrentHierarchyMember),
[Measures].[Issues created]
)
I`m not sure how to change the LastPeriods to reflect ‘lastSprints’, maybe you or someone else could help here.
@Marilou I do not know you but I think I love you. Your tip put me on a path and I got it to work as I needed. The example with LastPeriods was the clue. For anyone else, here is what I ended up with:
Avg(
LastPeriods(7, [Sprint].CurrentHierarchyMember),
[Measures].[Sprint Story Points completed at Sprint Close]
)
Thank you again.
@Marilou, thanks for sharing your solution. It is a good one!
I wanted to add some additional details on those metrics. The average calculation works on some set. There are different ways on how to define a set.
Our default formula Running Story Points velocity for 5 closed sprints works on all closed sprints from any board. It will pick sprints from other boards completed at the same time. Therefore, it might not work for scenarios if you are pulling in sprints from one board only and might show you misleading data. You would like to add some dimension, for example, project, to filter issues on board only (should match the board query). Then the formula will work as expected.
The solution suggested by @Marilou pulls in sprints based on their order in the hierarchy. Sprints are ordered by boards and then within by start date within the board. The formula might pick the last sprints from the previous board when you address the first sprints of the next board. However, it will work for most scenarios.
We have one example formula, Sprint running velocity in board in our demo account that will explicitly pull in sprints from one particular board. You can check it out in this example sprint velocity chart.
Here is how all three works based on the first sprints in some:
Please note there are some other ways on how to address sprints you would like to pull in for running velocity. For example, apply the running velocity on sprints visible in the report only.
Daina / support@eazybi.com
