Need to evaluate time in a priority

Questions & Answers
,
1

We use EasyBI for much of our reporting, including issue age. One of the things we look at is issue age by priority, but we have cases where an old lower priority issue is escalated and moved to a higher priority. In these cases, we need to understand the issue age by priority. As an additional complication, the aging also includes evaluating how long an issue is blocked:

So, we need to know:

  1. age of an issues since it was made a 2-High priority
  2. of that time, how long it was blocked.

the current custom measure for calculating blockage is:

[Measures].[Blocked Issues]

Case WHEN [Measures].[Open Issues (not Closed)] > 0
AND
DateAfterPeriodEnd(
now(), [Time].CurrentHierarchyMember.PrevMember
)
THEN
 Sum(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember
      ) 
      AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Closed at'),
        [Time].CurrentHierarchyMember
      )
    ),
    CASE WHEN
    (
    [Measures].[Issues history],
    [Transition Status].[Blocked statuses]
    )>0
    THEN
    1
  END
  )
END

where
[Transition Status].[Blocked Statuses]

Aggregate({
  [Transition Status].[Active],
  [Transition Status].[Awaiting Information],
  [Transition Status].[Owned],
  [Transition Status].[Awaiting Dependency],
  [Transition Status].[Awaiting FI Assistance],
  [Transition Status].[Blocked]
})

[Measures].[Avg Blocked Days]

-- Provided by EasyBI Support
Case WHEN [Measures].[Open Issues (not Closed)] > 0
AND
DateAfterPeriodEnd(
now(), [Time].CurrentHierarchyMember.PrevMember
)
THEN
  Avg(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember
      ) 
      AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Closed at'),
        [Time].CurrentHierarchyMember
      )
    ),
    CASE WHEN
    (
    [Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember
    )>0
    THEN
      CASE    
      --current period
        WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
        THEN
          ([Measures].[Days in transition status],[Transition Status].[Blocked Statuses],[Time].CurrentHierarchy.DefaultMember)
          +
          CASE WHEN
          [Measures].[Issue status] matches "Active|Awaiting Information|Owned|Awaiting Dependency|Awaiting FI Assistance|Blocked" --again blocked statuses to match for current status
          THEN
          DateDiffDays(
          [Measures].[Issue status updated date],
          Now()
          )
          END
       --current period ends   
      
       --previous periods
        ELSE 
        Sum(--sum starts here
            { PreviousPeriods([Time].CurrentHierarchyMember),
             [Time].CurrentHierarchyMember},
            ([Measures].[Days in transition status],[Transition Status].[Blocked Statuses])
            ) --sum ends here
            
            +
            Nonzero(
            iif(
            ( --if blocked status present at the end of displayed month
              [Measures].[Issues history],
              [Transition Status].[Blocked Statuses]
            )>0 
            AND
                        ( --if blocked status present at the end of displayed month
              [Measures].[Open Issues (not Closed)]
            )>0 
            
            , 
            Datediffdays(
                TimeStampToDate(MAX(
                    {PreviousPeriods([Time].CurrentHierarchyMember),
                     [Time].CurrentHierarchyMember},
                     DateToTimeStamp((
                     [Measures].[Transition to status last date],
                     [Transition Status].[Blocked Statuses]
                     ))
                    ))
                --last date when blocked status set
              ,
              [Time].CurrentHierarchyMember.NextStartDate --first date of next month
            ),0)  
            )
        
        --previous period ends
      END
  END
  )
END
2

Hi @EdP

eazyBI retrieves Priority changes by default. You may use hidden change history measures to access the date when last priority changes happened: measure “Transition to last timestamp” (to get the timestamp of the last date when the issue changed priority) and dimension “Transition Field” to check changes specifically in the priority field. Import issue change history

To calculate the issue age since it was moved to a 2-High priority, use DateDiffDays() function between the transition date and Now(). DateDiffDays

case when 
-- only for issues currently in High priority
[Measures].[Issue priority] = "High"
THEN
DateDiffDays(
  TimestampToDate(
   ([Measures].[Transition to last timestamp],
   [Transition Field].[Priority],
   [Priority].[High])),
   Now()
   )
end

Set integer or decimal formatting to show days. If you want to use duration formatting, use DateDiffMinutes() function instead.
Note, this calculation works if you have issues in the report rows!

Regarding blocked time. There won’t be an easy solution with MDX, as the calculation would be complex, slow, and inaccurate due to possible back-and-forth priority and blocked status changes we can not access using MDX.

The best would be if you could calculate time in blocked statuses while in High priority, using calculated JavaScript custom field. With JavaScript, you would go through the issue changelog and find blocked time in this particular priority. It is important to calculate this time only when the issue has left blocked statuses, for issues still in blocked statuses, the calculation won’t be correct (the result would stale after data import). JavaScript calculated custom fields

Best,
Ilze, support@eazybi.com

3

@ilze.leite This has been a huge help! How could we do this to say age since created as a P2 (and still is a P2) or age since it changed to a P2?