EazyBI backlog between two states

[Ernesto Garcia del Moral]

Hi, how could I get the backlog between to different status which are in between the normal lifecycle. 

First state is [TO DO] I want to count issues in this status since the first transition to this state and this would be equivalent to issues created

Last State if [TEST] I want to count issues from this state's last transsition date and this wpuld be equivalent  to "closed"

The backlog would be the issues in between.

I've seen many related examples but I'm not able to find the correct one.

Thanks

[Daina Tupule eazyBI]

eazyBI by default has representation for the first status. Measure Issues created represents all created issues by creation date and property Issue created date will represent the exact date and time of an issue creation.

You can use measure Issues closed and property Issues closed date for counting issues by closing status if this status is Test. You would like to set any closing statuses in import options in this case.

You can use this formula (based on default calculation Open issues) where Closing date and measure is used instead of resolution date and measure:

CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN
Cache(
NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
Cache([Measures].[Issues created]
- [Measures].[Issues closed])
))
+ [Measures].[Issues created]
- [Measures].[Issues closed]
)
ELSE
-- optimized formula for drill through Issue
NonZero(IIF(
DateBeforePeriodEnd(
[Issue].CurrentMember.get('Created at'),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get('Closed at'),
[Time].CurrentHierarchyMember),
([Time].CurrentHierarchy.DefaultMember,
[Measures].[Issues created]),
0
))
END

The similar formula you can use if you have a date custom field in Jira you are importing it as a measure and as a property into eazyBI.

For more generic cases, you can use historical measures and dimension Transition Status. Please check if you are importing issue change history, though.

Here is an example of this:

CASE WHEN
[Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
THEN 
[Measures].[Issues created]
-
([Measures].[Transitions to status issues count],
[Transition Status].[Test])
ELSE
-- calculate backlog for any period
NonZero(Count(Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateBeforePeriodEnd(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember
)
AND
IIF(
isempty(([Measures].[Transition to status last date],
[Transition Status].[Done],
[Time].CurrentHierarchy.DefaultMember)),1,
DateAfterPeriodEnd(
([Measures].[Transition to status last date],
[Transition Status].[Test],
[Time].CurrentHierarchy.DefaultMember),
[Time].CurrentHierarchyMember
))
AND
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0
)))
END

This formula might work slow. 

Daina / support@eazybi.com