Average time in backlog

Hi all,
I’m trying to calculate the average time an issue spent in an idle condition before being dragged in a sprint.
I’m taking some ideas from here:

The point is, I have the dates, but I cannot roll the data up to get a project-based average difference between creation and the earliest sprint start date.

[Edit] I went somehow on with the measures, and I used:

measures.Issue Earliest Sprint Start Date:

CASE
WHEN [Issue].CurrentHierarchyMember.Level.Name = ‘Issue’
THEN
Order(
[Sprint].[Sprint].getMembersByKeys(
[Issue].CurrentHierarchyMember.get(‘Sprint IDs’)
),
[Sprint].CurrentHierarchyMember.Get(‘Start date’),
BASC
).item(0).get(‘Start date’)
ELSE
[issue].CurrentHierarchyMember.level.Name
END

measures.time in backlog:

DateDiffDays([Measures].[Issue created date], [Measures].[Issue Earliest Sprint Start Date])

measures.average days in backlog:

case
when [issue].CurrentHierarchyMember.level.Name = ‘Project’
THEN
Avg(descendants([Issue].CurrentHierarchyMember, [issue].[issue]), [Measures].[Time in Backlog])
ELSE
[Measures].[Issues created]
END

I’d need to make the last one time-dependent, so that I could track the average time in backlog - let’s say - per month (based on issue creation date).

Another bit, what if I had to filter issues by type?
If I do the maths by project, there’s no way page filters would work.

Any help?
Thanks

Hi @Mauro_Bennici ,

I am happy you already found part of the solution :+1: . I think the ELSE branch is not required in the calculated measure “Issue Earliest Sprint Start Date”. The formula could look similar to the one below:

CASE WHEN [Issue].CurrentHierarchyMember.Level.Name = 'Issue'
THEN
Order(
    [Sprint].[Sprint].getMembersByKeys(
      [Issue].CurrentHierarchyMember.get('Sprint IDs')
  ),
  [Sprint].CurrentHierarchyMember.Get('Start date'),
  BASC
).item(0).Get('Start date')
END

I recommend improving the average calculation inside the calculated measure “average days in backlog” with the Filter() function. Retrieve only the issues in any Sprints and include a numeric measure to tie the report’s different perspectives together. See the suggested formula below:

CASE WHEN [ISsue].CurrentHierarchyMember.Level.Name <> 'Issue'
THEN
  Avg(
    Filter(
      Descendants([Issue].CurrentMember,[Issue].[Issue]),
      Not IsEmpty([Issue].CurrentHierarchyMember.Get('Sprint IDs'))
      AND
      [Measures].[Issues created] > 0
    ),
    [Measures].[time in backlog]
  )
ELSE [Measures].[time in backlog]
END

The calculated measure will return the number of average days in backlog and the specific number of days if the Issue dimension is in the “Issue” level. See the result in a report:

See more details on the AVG function on the eazyBI documentation page - Avg - eazyBI.

Best,
Roberts // support@eazybi.com

@roberts.cacus Thanks very much!