I have a series of charts like “Burndown - Current Sprint”, “Burndown - Last Sprint”, etc. I am doing this by selecting and bookmarking the sprint members manually (pictured). The downside to this is that, every two weeks, I have to manually edit these three charts by selecting the next sprint.
I would like to have this done automatically. How can I write a function to select a member similarly to the following human instructions:
- “Return the sprint that is active today”
- “Return the sprint that was active on (today - 14 days)”
- “Return the sprint that was active on (today - 28 days)”
1 Like
Hi,
You can create the calculated member in the Sprint dimension giving dynamically sprints by some condition. For instance, following formula would give you the ordered list of all active sprints currently (there might be several of them):
Aggregate(
Order(
Filter(
[Sprint].[Sprint].Members,
NOT [Sprint].CurrentMember.getBoolean('Closed')
AND
NOT isEmpty([Sprint].CurrentMember.get('Start date'))
),
-- ordering by start date across all boards
[Sprint].CurrentMember.get('Start date'), BASC
)
)
This calculation gives the ascending list ordered by the Sprint start date.
Similarly, you can extend the formula by a condition of when the sprint is completed to find the sprints active in some of the previous periods and apply the “Item(0)” function to get the first sprint from the list:
Aggregate(
Order(
Filter(
[Sprint].[Sprint].Members,
[Sprint].CurrentMember.getBoolean('Closed')
AND
DateBetween([Sprint].CurrentMember.get('Complete date'),'14 days ago','now') ),
-- ordering by complete date across all boards
[Sprint].CurrentMember.get('Complete date'), BDESC
).Item(0)
)
Note the “DateBetween” function in the condition where you can set the date range dynamically for the period you are interested.
You can also check our demo account for some more ideas of how to create members in the Sprint dimension: https://eazybi.com/accounts/1000/cubes/Issues.
Kindly,
Janis, eazyBI support
Is there a way to filter this list by project? I would like to create a burndown with this current sprint value (so that we don’t have to change the sprint every two weeks). Selecting the top value often results in the most recent sprint overall, not the most recent sprint by project.
Thanks!
I have the same question. I want to get the most recently closed sprint for each project.
Hi,
There is not an efficient solution for selecting the last closed “sprint of the project”. Jira does not hold the direct links between the Project and Sprint which makes it harder to reproduce such a link in eazyBI.
The workaround in eazyBI could be to create a custom measure which finds the latest sprint and shows the value of the measure for the last sprint which is related to the selected project. For instance, this formula shows the “Sprint story points committed” measure for the last sprint of the selected project in report pages:
CASE WHEN
[Sprint].CurrentHierarchyMember IS
Order (
Filter( [Sprint].[Sprint].Members,
[Measures].[Issues created]>0),
[Measures].[Sprint complete date],
BDESC
).Item(0)
THEN
[Measures].[Sprint Story Points committed]
END
I can apply the filter for the report on this measure and automatically the results for the latest sprint in the report:
Kindly,
Janis, eazyBI support
Hi,
Sorry for reactivate this post, but I have a similiar issue that I’m having trouble to solve.
I have a dashboard with some graphs that uses a common project filter, as you can see above:
and I need to select the active sprint based on that selection, that is, when someone change the select box on top of that dashboard, I would like that the “Sprint Story Points burndown chart” automatically shows the active sprint of selected project.
Anyway, thanks for this post, it helps me a lot.
Kindly,
Ricardo Tulio.
how could i filter by a specifc board ?
Hi,
you can use the Sprint dimension in report rows and pages. Select the board level from the pages:
Kindly,
Janis, eazyBI support
