How to calculate Days in transition status including the time in current status

mdx
jira

#1

By default, the measure Days in transition status calculates the time (in days) only for completed workflow transitions and therefore it can’t be used to calculate the time in the current status.

However, the following MDX formula (use it to define a new calculated member in Measures) will include into the calculation also days in the current status:

NonZero(
  Cache(
    -- days in transition status when issue was in this status previously
    [Measures].[Days in transition status] +
    -- days since last transition to this status
    IIF([Measures].[Issues history] > 0,
      DateDiffDays(
        ( [Measures].[Transition to status last date],
          [Time].CurrentHierarchy.DefaultMember ),
        Now()
      ), 0
    )
  )
)

Time between to actions
#2

Hi

can the same formula that can also calculate the average days in transition including the time in current status?

thanks for your help
arige


#3

Hi,

The approach would be the same, just the code would slightly differ.

Try this one (it requires using also “Transition Status” and “Issue” dimensions in the report)

CASE WHEN
 [Measures].[Issue status]=[Transition Status].CurrentMember.Name
THEN
 (DateDiffWorkdays([Measures].[Transition to status last date],
 Now())
 +
 [Measures].[Days in transition status])/
 ([Measures].[Transitions from status]+1)
ELSE
 [Measures].[Days in transition status]/
 [Measures].[Transitions from status]
END

Martins