I am currently using the following query to calculate lead time for an issue type:
CASE WHEN
(
[Measures].[Transitions to status],
[Transition Status].[QA Complete]
) > 0
THEN
AVG(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod(
([Measures].[Transition to status last date],
[Transition Status].[QA Complete],
[Time].CurrentHierarchy.DefaultMember),
[Time].CurrentHierarchyMember
)
),
DateDiffWorkDays(
([Measures].[Transition from status first date],
[Transition Status].[in progress],
[Time].CurrentHierarchy.DefaultMember),
([Measures].[Transition to status last date],
[Transition Status].[QA Complete],
[Time].CurrentHierarchy.DefaultMember)
)
)
END
What i noticed is that it disregards any Jira ticket where the ticket never left the “in progress” status. There is no restriction in the workflow to say all issues have to go straight into in progress. Is there a way to adapt the above formula so it calculates the lead time from first date an issue type has left an array of statuses? i.e. if an issue type went from either Backlog or In refinement or ready for development or in progress to QA complete.
DateDiffWorkDays(
([Measures].[Transition from status first date],
[Transition Status].[in progress],
[Time].CurrentHierarchy.DefaultMember),
([Measures].[Transition to status last date],
[Transition Status].[QA Complete],
[Time].CurrentHierarchy.DefaultMember)
)
you measure the difference between [Transition Status].[in progress] and [Transition Status].[QA Complete], that’s why you don’t get the others. Change [Transition Status].[in progress] to whatever status yo want to measure.
so the issue is really that an issue doesnt have to go to in progress so if it doesnt then it doesnt get calculated… Theres no workflow constraint to say that issue needs to go to in progress before QA complete. So i was thinking whether we could look at saying calculate the leadtime from when a issue type has left either of XYZ Statuses from first date to QA Complete.
sure you can do that, you just have to know from which status you want to calculate. The formula you are using is using the date of leaving “In progress”. I guess it is a bit tricky to find all issues, that have never been in status “In progress”, but it should be possible via usage of tupels. For those you use the status before and for all others the formula above and then add both values up.
Any Eazy bi staff able to help if its possible to configure my report / measure to calculate lead time from when a issue has left any range of statuses from first date to last date it went to QA Complete
Please double-check the names of statuses on your Jira instance.
You might then use this calculated member to find the first timestamp from any of these statuses.
TimeStampToDate(
MIN(
{PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember},
DateToTimeStamp(
([Measures].[Transition from status first date],
[Transition Status].[4 Statuses]))
)
)
Then the whole expression might look as follows.
CASE WHEN
([Measures].[Transitions to status],
[Transition Status].[QA Complete]) > 0
THEN
AVG(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
--condition for filter
DateInPeriod(
([Measures].[Transition to status last date],
[Transition Status].[QA Complete],
[Time].CurrentHierarchy.DefaultMember),
[Time].CurrentHierarchyMember)
),
--actual measure for AVG
DateDiffWorkDays(
TimeStampToDate(
MIN(
{PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember},
DateToTimeStamp(
([Measures].[Transition from status first date],
[Transition Status].[4 Statuses]))
)),
([Measures].[Transition to status last date],
[Transition Status].[QA Complete],
[Time].CurrentHierarchy.DefaultMember)
)
)
END