Calculating Rolling Cycle Time Percentage

Questions & Answers
1

Hi,
I’m creating Cycle time charts based on JIRA data.
I defined a measure ([Measures].[Cycle time at move to Done]) which presents the cycle time of an issue, at the time it moved to “Done”.

I am able to present a timeline with cycle times scattered on dates (the report has both time and issues on its rows) and with a line representing 80% of the cycle time for the entire selected period (e.g., 14 days).
I want to add a measure of rolling 80% line to present, for each day, the 80% of all cycle times over the issues that moved to “Done” during the 7 days preceding the date (actually 6 preceding and the date itself).

When I calculate the new measure this way:

CASE WHEN NOT IsEmpty([Measures].[Cycle time at move to Done])
THEN
Percentile(
  -- set of the dates in the period starting 6 days ago and
  -- ending today (?)
  LastPeriods(7, [Issue].[Time].CurrentMember),
  -- number of days spent in category "Doing"
  [Measures].[Cycle time at move to Done]
  ,
  -- 80th percentile
  80
)
END

I get the cycle time of issues, rather than 80 percent of the cycle time over 7 days. In particular I get different values for issues on the same date.

I’ll appreciate help in achieving the rolling 80%.

Thanks,
Barak

2

Hi,

The Percentile function requires iterating all the Issues in scope.

For instance, the following formula calculates the 80th percentile cycle time for issues having transitioned to done status in the last seven periods.

Percentile(
Filter(
  Descendants([Issue].CurrentMember,[Issue].[Issue]),
   Sum( LastPeriods(7,[Time].CurrentHierarchyMember),
   ([Measures].[Transitions to status],
    [Transition Status].[Done])
  )>0  
),
[Measures].[Cycle time at move to Done]
,
80
)

Kindly,
Janis eazyBI support

3

Thanks Janis,

The reason I couldn’t use Descendants([Issue].CurrentMember,[Issue].[Issue]) is because my rows have both time and issue dimensions, so Descendants([Issue].CurrentMember,[Issue].[Issue]) creates a set that only includes the [Issue].CurrentMember.
With the help of the support team I ended up writing the following, that provided what I needed.

Percentile(
  -- set of the issues that moved to Done in the period starting
  -- 6 days ago and ending today
  Filter(
    -- all issues
    [Issue].[Issue].Members,
    -- that moved to Done between start of the day 6 days ago and
    -- start of day tomorrow
    DateBetween(
      [Measures].[Done starts],
      DateAddDays([Time].CurrentHierarchyMember.StartDate, -6),
      [Time].CurrentHierarchyMember.NextStartDate
    )
  ),
  ([Measures].[Cycle time],
  -- the following is required to include cycle times that occur
  -- before the selected/visible period
   [Time].DefaultMember),
 80
)

The only issue, I now face, with this masseur is that it is slow to calculate.

Thanks again,
Barak

1 Like