Transition to status first date breaks whenever I try to aggregate

Questions & Answers
, , ,
1

I have two different reports. In one of them, I have a scatter plot of tickets, x axis is dates, y axis is the number of hours it took for the ticket to get in “In Progress” for the first time. For this, I have a calculated measure with the following script:

  DateDiffHours(
    [Measures].[Issue created date], 
    ([Measures].[Transition to status first date],
    [Transition Status].[In Progress])
    )

Works like a charm.
BUT: I have another report, where I want to aggregate all this data for each month and calculate a median from all the issues in that month. For this, I use the following script:

Median(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    NOT IsEmpty(
      ([Measures].[Transition to status first date],
       [Transition Status].[In Progress])
    )
  ),
  DateDiffHours(
    [Measures].[Issue created date],
    ([Measures].[Transition to status first date],
     [Transition Status].[In Progress])
  )
)

This is calculating the LAST time my issue got moved into in progress. So whenever a ticket goes to On Hold, Impeded or whatever, and then goas back to In Progress, my report uses that date instead of the very first one. How is this possible? I can’t see how an aggregation can mess up the same calculation. Unfortunately, I don’t know how to go deeper in debugging.
Thanks

2

Hi @Gergo_Cser-Kovacs

Thanks for posting your question!

Indeed, your first case works great because you have issue dimension and the calculation is evaluated in the context of an issue.
When you start introducing Time in your report, the results are then grouped by months and the Median() function is evaluated within a specific month context, the “Transition to status first date” measure looks for transitions within that month’s timeframe. Without explicitly resetting the Time dimension context, it may my seem that it is returning the last transition to the status, but in fact, it is the first in the context of the selected time period.

Here are my suggestions you may want to try:
If you want median time-to-progress and group the results by issue creation date:

Median(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentHierarchyMember.get('Created at'),
      [Time].CurrentHierarchyMember
    )
    AND
    NOT IsEmpty(
      ([Measures].[Transition to status first date],
       [Transition Status].[In Progress],
       [Time].CurrentHierarchy.DefaultMember)
    )
  ),
  DateDiffHours(
    [Measures].[Issue created date],
    ([Measures].[Transition to status first date],
     [Transition Status].[In Progress],
     [Time].CurrentHierarchy.DefaultMember)
  )
)

If, however, you would like to group the issues by when they first moved to in Progress status, you may try the below calculation

Median(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    NOT IsEmpty(
      ([Measures].[Transition to status first date],
       [Transition Status].[In Progress])
    )
    AND
    DateInPeriod(
      ([Measures].[Transition to status first date],
       [Transition Status].[In Progress]),
      [Time].CurrentHierarchyMember
    )
  ),
  DateDiffHours(
    [Measures].[Issue created date],
    ([Measures].[Transition to status first date],
     [Transition Status].[In Progress],
     [Time].CurrentHierarchy.DefaultMember)
  )
)

Best wishes,

Elita from support@eazybi.com