"Time in Status" report always showing all work items

Questions & Answers
, ,
1

Hello everyone,

I am running into a problem with my report that I would like to create and I cannot figure out what the issue could be…

What I need is a report that gives back the Avg time in hours an already resolved ticket has been in the statuses “todo”,“Open Lvl.1”, “Open Lvl.2”, “In Progress lvl.1” and “In Progress Lvl.2” added up

Avg(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    NOT IsEmpty([Measures].[Issue resolution date])
    AND
    Sum(
      {[Transition Status].[TODO],
       [Transition Status].[Open lvl.1],
       [Transition Status].[In Progress lvl.1],
       [Transition Status].[Open lvl.2],
       [Transition Status].[IN PROGRESS LVL.2]},
      ([Measures].[Days in transition status],
       [Time].CurrentHierarchy.DefaultMember) * 24
    ) > 0
  ),
  Sum(
    {[Transition Status].[TODO],
     [Transition Status].[Open lvl.1],
     [Transition Status].[In Progress lvl.1],
     [Transition Status].[Open lvl.2],
     [Transition Status].[IN PROGRESS LVL.2]},
    ([Measures].[Days in transition status],
     [Time].CurrentHierarchy.DefaultMember) * 24
  )
)

This gives me an average of all the issues ever resolved in our Jira project, and I don’t know why…

Is there someone who could help me understand what is going on?

Thanks in advance,
Best
Christoph

2

Hi @DaSeidl,

The issue with your report is in the filter condition.
You’re currently using:

NOT IsEmpty([Measures].[Issue resolution date])

This condition includes all resolved issues regardless of when they were resolved, which is why you’re seeing data for all issues that have ever been resolved in your Jira project.

To filter the report by the selected time period, you should use DateInPeriod function instead:

Avg(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    DateInPeriod([Measures].[Issue resolution date],
      [Time].CurrentHierarchyMember)
  ),
  Sum(
    {[Transition Status].[TODO],
     [Transition Status].[Open lvl.1],
     [Transition Status].[In Progress lvl.1],
     [Transition Status].[Open lvl.2],
     [Transition Status].[IN PROGRESS LVL.2]},
    ([Measures].[Days in transition status],
     [Time].CurrentHierarchy.DefaultMember) * 24
  )
)

The DateInPeriod function will only include issues that were resolved within the time period selected in your Time dimension (e.g., “Last 12 months”, “Current month”, etc.).

Best,
Marita from support@eazybi.com

3

Thank you very much! This solved my problem :slight_smile:

Best,
Christoph

1 Like