Average time at issue history

Hi everybody!
I’m trying to get the average time of issues, which was in current status at current date in the past.
For example: I have 25 issues which were in status “To Do” and 2 in status “In progress” on Dec 14 2020.
and I have measure:

NonZero(
 Cache(
   nonzero( IIF([Measures].[Issues history] > 0,
      DateDiffDays(
       ( [Measures].[Transition to status first date],
          [Time].CurrentHierarchy.DefaultMember),
        [Time].CurrentHierarchyMember.NextStartDate
        ), 0
    )
  ))
)

Which correctry calculate time for each issue, when I opent it in “Drill”.

but total score is 423.4, but must be about 23.

Can you help me with correct measure? And what I did wrong?

Hi @Garryz,

Measures “Transition to status first date” always returns the earliest date possible. And for individual issues is works fine as it has a particular date and time of transition. However, when you have a set of 25 issues, then measure does the same thing and returns the earliest date of transition from the whole set of issues.

To make this work, you might want to update the add functions Avg() and Descendants() to the calculation. This way, the calculation can go through individual issues (using function Descendants ) and check on duration in status for each issue (the calculation that already works for you) and then get the average over the set of issues (using function Avg ).
The updated formula might look like this:

Avg(
  --set of issues that were in selected status at the end of period
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    [Measures].[Issues history] > 0),
  --for each issue calcaulte the duration
  DateDiffDays(
    ( [Measures].[Transition to status first date],
      [Time].CurrentHierarchy.DefaultMember),
    [Time].CurrentHierarchyMember.NextStartDate)
  )
)

Note that condition [Measures].[Issues history] > 0 now is used as filter criteria for issues.

More details on aggregate functions and mentioned functions are here:

Best,
Zane / support@eazyBI.com