Calculated Measure - Percent of Time in Blocked Status

Questions & Answers
,
1

I’m trying to report on a specific metric on a cube of Jira issues, but my limited EasyBI skills are hampering my progress. Here’s the measure that I’m trying to analyze with EazyBI:

Numerator: Total days for selected team-level issues types spend in Blocked status
Denominator: Total days for selected team-level issues types spend in sprint statuses

Where:

  • “selected” is based on issues resolution date being in a specified period
  • “team-level issues” are the set of issue types used by sprint teams to manage their sprint work or Story, Bug, Task, Spike, Enabler.
  • “sprint statuses” are the statuses used by sprint teams to manage their sprint work (about 10 specific statuses)

My goal is to compare the various period used to “select” the issues to see if we are getting better at removing/avoiding blockers. My challenge is that the “Time in Status” measure in EazyBI is linked to the time dimension, so filtering by time only reports on the time in statuses that falls during the filter. Instead, I want to filter the set of issues by resolution date them report on the entire status lifetime of each included issue, regardless of when those status transitions happened.

Please forgive any basic newbie misunderstandings. I’m just starting to comprehend EazyBI and MDX. Any help would be appreciated.

2

eazyBI creates a set of default measures counting days spent in statuses - Days in transition status and Average days in transition status. However, you are correct. Those measures count the total time spent in the status at the moment when an issue moves out of status.

You might need to use a custom measure to count the total time spent in several statuses. You are correct, you would like to use one common date to compare different cycles - Active work and Blocking. The resolution date is one of our suggestions as well.

Could you check this report example Average lead and cycle time in our demo account? It uses several default measures and several custom measures to show differences in how you can analyze time spent on issues. Please check the measure Average days in cycle in particular. This calculated measure will count the total time per issue for a list of specified statuses per resolution date and then will give you an average of all issues resolved in a period.

Here are steps on how you can apply it to your use case.

  1. Create a new measure using the formula from our demo account Average days in cycle. You can replace a function Avg with Sum to get the Total days in cycle by issue resolution days.
  2. Create two new calculated members in the dimension Transition status.
    You can use the function Aggregate and list the sprint statuses. See example formula in the same report Custom cycle in the dimension Transition status:

for example, this one for Sprint statuses

Aggregate(
  {[Transition Status].[To Do],
  [Transition Status].[In Progress]}
)

Then create another one for Blocked and list the blocked statuses.

  1. After this, you can calculate the Total time in Sprint status and Total time in Backlog, here is an example for Total time in Sprint status:
([Measures].[Total days in cycle],
[Transition Status].[Sprint statuses])
  1. Then you can get the % of the time in blocked status with this formula:
CASE WHEN
[Measures].[Issues resolved] > 0
THEN
[Measures].[Total time in Backlog ]
/
[Measures].[Total time in Sprint status]
END

Daina / support@eazybi.com

3

@daina.tupule Thanks for the guidance. I ended up using a three custom measures. A numerator, denominator and quotient of the two. The numerator (blocked status) is defined as:

NonZero(Sum(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
    )),
    CASE WHEN 
     -- filter currently resolved issues for specific issue types, taking into account all selecte dimensions in the report, except transition status
      ([Measures].[Issues resolved], [Issue type].[Story], [Transition Status].CurrentHierarchy.DefaultMember) > 0
    THEN
      NonZero(
        DefaultContext((
         [Measures].[Days in transition status],
         [Issue].CurrentMember,
         [Transition Status].[Blocked],
         [Status.Category].[Done]))
      )
    END
  ))

The denominator (time in all sprint statuses) is nearly identical, just replacing “Blocked” with ‘DaysInSprint’ (the name of a custom aggregate member of the sprint statuses).

Since either/both of these could be zero, the ratio of blocked to sprint I defined as

CASE 
WHEN ([Measures].[DaysInSprint] > 0)
  THEN CoalesceEmpty(Measures].[DaysInBlocked],0) / [Measures].[DaysInSprint]
END

Thanks again for the help.

4

It is great you could update the measures for your needs.

Some tips on performance. The setup requires issue-level calculations. They might work slowly in larger accounts. We suggest using properties for filters whenever possible.

You are using Issue type Story as a filter with measure Issues resolved. You can use it there, But consider adding it as a property Issue type to the filter directly. It will minimize the set of issues better.

NonZero(Sum(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
    )
   AND  [Measures].[Issue type] = "Story"
   ),
    CASE WHEN 
     -- filter currently resolved issues for specific issue types, taking into account all selecte dimensions in the report, except transition status
      ([Measures].[Issues resolved], [Issue type].[Story], [Transition Status].CurrentHierarchy.DefaultMember) > 0
    THEN
      NonZero(
        DefaultContext((
         [Measures].[Days in transition status],
         [Issue].CurrentMember,
         [Transition Status].[Blocked],
         [Status.Category].[Done]))
      )
    END
  ))

The formula calculation % of blocked vs time in the sprint will apply a complex calculation Days In Sprint twice:

CASE 
WHEN Cache(([Measures].[DaysInSprint]) > 0)
  THEN CoalesceEmpty([Measures].[DaysInBlocked],0) /Cache([Measures].[DaysInSprint])
END

The best would be using measure Issues resolved as a filter in the formula above. However, I hear you. If some resolved issues might have value empty or 0 for daysinsprint, the formula will give infinity as a result.

You can use a cache function (I added in the formula above already). The cache function should be used with caution. The cache itself can take additional resources. We suggest adding it for complex calculations if the calculation will execute more than once in the same report.

If the report starts to work slow you can also consider calculating the cycle days during import with calculated javascript custom fields.

Daina / support@eazybi.com