Showing Average delivery time, grouped by creation date

Questions & Answers
1

Hi,

I’m looking for a calculated measure or a solution which allows me to look at the average delivery time of a ticket over time. I would like to show this metric by looking at the creation date of the tickets, to get a feeling if we’re progressing over time (Unfortunately, due to the nature of this project, the tickets don’t have a fix version or some kind of release indication).

e.g. Calculate the Average delivery time for all the tickets created in the month of August / September / …

Goal: we want to measure if some of the process changes are making the teams more efficient, hence the interest in looking at the creation date of the tickets - as it becomes easier to relate to the changes in the operating model.

Observed / assumed behaviour: when plotting the average delivery timeline over time, without a dedicated calculated measure, I think the tool will include all the tickets which are open for that period.

I’ve created the calculated measure as presented below - it has some additional filters which are less relevant for tis topic, but these are confirmed to be working in other reports. Furthermore, I have the calculation to give me the average delivery time (works well and has been validated)

NonZero(Avg(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
  DateInPeriod(
    [Issue].CurrentMember.get('Created at'),
    [Time].CurrentHierarchyMember) 
  AND
    (
    [Measures].[Issue type] = "Technical Epic" 
    OR
    [Measures].[Issue type] = "Technical Story"
    )
  AND
  ([Measures].[Transitions to status issues count],
[Transition Status].[Ready for Handover]
)>0 --checks if issue went to In handover status
),
[Measures].[CSA Total Idle and Progress]
))

The measure above gives me unexpected results, so not sure what the best implementation would be for such a metric.

Thanks!

2

Hi @Stijn,

The calculated measure could return unexpected results because of the other measures referenced in it. “Transitions to status issues count” and possibly “CSA Total Idle and Progress” measures are also tied to the Time dimension by particular dates. I recommend resting the Time dimension context for these measures.

I also recommend some optimization to improve the calculation performance. The calculated measure formula could look similar to the one below:

NonZero(Avg(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- issue created in period
    DateInPeriod(
      [Issue].CurrentMember.get('Created at'),
      [Time].CurrentHierarchyMember
    )
  ),
  CASE WHEN
    -- checks if issue went to In handover status
    ([Measures].[Transitions to status issues count],
    [Transition Status].[Ready for Handover],
    [Time].CurrentHierarchy.DefaultMember)>0
  THEN
  -- combine issue type filter and the value
  Aggregate(
    {[Issue Type].[Technical Epic],
    [Issue Type].[Technical Story]},
    ([Measures].[CSA Total Idle and Progress],
    [Time].CurrentHierarchy.DefaultMember)
  )
  END
))

Best,
Roberts // support@eazybi.com

3

Great, this solution worked for me but also very insightful feedback, appreciated a lot!

1 Like