How to tracking the Sprint Ready Backlog across Time

Questions & Answers
1

I’m looking for some help to create a historical view of our scrum teams’ sprint ready backlog.

Basically I want to sum the total Story Points for all Stories that were in a ready status at the end of a time period, that have been in a ready status for less than 90 days. Then divide the number of story points by the scrum team’s velocity

We have a custom date field ‘Date In Ready’ that I can use to determine the last time a ticket was placed in a ready status. Because this is time based and not sprint based, I need to exclude stories that are in a ready status and assigned to the sprint that was active at the end of the time period.

This is how I’ve broken down the conditions:
Field: ‘Issue Type’ is Story
Field: ‘Status’ was in ready at the end of the time period
Field: ‘Custom Date [Date In Ready]’ was dated within 90 days of the end of the time period
Field: ‘Sprint’ is not assigned OR ‘Sprint’ Start date is after the time period

The JQL would look something like this:
project = EXP
AND IssueType = Story
AND STATUS WAS IN (‘READY’) ON 2020-08-31
AND “Date In Ready” < 2021-09-01
AND “Date In Ready” > 2021-06-01
AND (SPRINT NOT IN (‘EXP Sprint10’) OR SPRINT IS EMPTY)
AND issueFunction in aggregateExpression(“Total Estimate for all Issues”, “storypoints.sum()”)

Page Filters: Project, Time Range
Rows: Time Period Increments (e.g. monthly)
Column: Estimated number of sprints worth of work in the sprint ready (<90days) backlog

Any help or guidance would be appreciated. I’m stuck after working through the following:

  1. The imported EazyBI Measures ‘Issues with date in ready’ and ‘Story Points with date in ready’ only count the issues with a Date in Ready during the time period.
  2. I’m not sure how to filter my backlog Tuples (code below) by Issue Property: ‘Issue Date In Ready’
  3. I haven’t figured out how to exclude issues that were in the active sprint at the end of the time period
  4. My Sprint Velocity is off.

Thanks!
Jon

Code I have been working through below:
‘’’
[Measures].[Ready Status]
–Ready Status Count
([Measures].[Issues history],
[Issue Type].[Story],
[Transition Status].[Ready])
‘’’

‘’’
[Measures].[Ready Points]
–Ready Status Points
([Measures].[Story Points history],
[Issue Type].[Story],
[Transition Status].[Ready])
‘’’

‘’’
[Measures].[ 5 Sprint Average Story Points ]
– average velocity from last 5 closed sprints
Avg(
Tail(
Order(
– filter last 5 closed sprints
Filter(
Generate(
{ [Sprint].CurrentHierarchyMember,
ChildrenSet([Sprint].CurrentHierarchyMember) },
Descendants(
[Sprint].CurrentHierarchyMember,[Sprint].[Sprint] )
),
– only sprints with committed story points are retrieved
[Sprint].CurrentMember.getBoolean(‘Closed’) AND
NOT IsEmpty([Sprint].CurrentMember.get(‘Complete date’)) AND
([Measures].[Sprint Story Points completed],[Time].CurrentMember) > 0
),
– order by completion date
[Sprint].CurrentMember.get(‘Complete date’), BASC ) ,
5),
[Measures].[Sprint Story Points completed]
)
‘’’

2

Hi @Jon

You would need a custom calculation that iterates through imported issues for each sprint.

Try something like this to count completed story points for the issues in ready status at the end of time period and with date in ready between your two required dates.

NonZero(
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
Datebetween(
	[Measures].[Issue date in ready],
	'2021-06-01',
	'2021-06-01')
AND
([Measures].[Issues history],
[Issue Type].[Story],
[Transition Status].[Ready])>0
),
[Measures].[Sprint story points completed]
)
)

Then you would use the new measure for the Velocity calculation instead of Sprint Story Points completed.

There I am suggesting a formula Descenants is heavy because it iterates through all imported members in the “Issue” dimension, therefore it can be much slower than using regular tuples.
Please enable the “Nonempty” cross join in the report for these calculated measures.

See similar questions here:

Martins / eazyBI support

1 Like