Workdays in Transition Status only for the days during a particular Sprint

Questions & Answers
1

Hello,
I have spent days reading through this community and online to try and find the answer but I still have not been able to find it:

I have a report that has Rows for [Sprint], [Issue], [assignee]. Measures for [story points], and then the desire is to show measures for how many workdays each issue was in a particular status ONLY during the Sprint that is on that row for that issue.

[Workdays in Transition Status] keeps giving me the total history. Example: A Sprint that took 19 total workdays to complete may show a [Transition Status] of [In Progress] for 144 days when it is really 3 during that Sprint. Because I have the Sprint as the row, I have access to the Sprint Start and End dates. I would like to use the measure [Workdays in Transition Status] and calculate only the days the issue was in that status during the Sprint.

2

Hi @Jeff_Tabb

“Workdays in transition status” measure that you use in your formulas would not be limited within the sprint timeframe.
It would calculate the total time (workdays) in transition status from the first time issue went to status (regardless of the sprint timeframe) until it went out from the status (during the sprint).

There is no good solution to find the time in status within the particular sprint.

The closest would be calculating the workdays since the issue went into the status for the first time within the sprint timeframe until it went out from the status last time within timeframe
Or from the sprint actual start (if issue was in status already before sprint started) until the last time issue went out from the status (or until sprint actual end date if issue didn’t go out from the status within sprint timeframe).
See one example of how to calculate “Workdays In progress (in sprint)”

CASE WHEN
IsEmpty(
        (
        [Measures].[Transitions to status],
        [Transition Status].[In Progress]
      )
)
THEN
Datediffworkdays(
  [Measures].[Sprint actual start date],
  iif(
      (
        [Measures].[Transitions from status],
        [Transition Status].[In Progress]
      )>0,
      (
        [Measures].[Transition from status last date],
        [Transition Status].[In Progress]
      ),
     [Measures].[Sprint actual end date]
  )
)
ELSE
Datediffworkdays(
        (
        [Measures].[Transition to status first date],
        [Time].CurrentHierarchy.DefaultMember,
        [Transition Status].[In Progress]
        ),
      iif(
      (
        [Measures].[Transitions from status],
        [Transition Status].[In Progress]
      )>0,
      (
        [Measures].[Transition from status last date],
        [Transition Status].[In Progress]
      ),
     [Measures].[Sprint actual end date]
    )
)
END

Martins / eazyBI