EazyBI Formula - remaining time of issues at the start of sprint

Questions & Answers
1

Dear Community,

We are currently evaluating EazyBI for JIRA.
We need a formula to calculate a column with the values of remaining time of each sub-task at the time of a specific sprint started (not including changes of issues’ remaining time during the sprint).
More precisely, I need to know the remaining time of sub-tasks of a sprint at the start time of the sprint.

Regards,
Ali

1 Like
2

Hi,

The recommended approach for calculation of the remaining estimate for tasks at the start of the sprint would be trying to implement a measure similar to the formula under the “Story points committed” measure. There is a hidden measure in eazyBI data model “Remaining estimated hours added” for that and the formula is the following:

( [Measures].[Remaining estimated hours added],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  [Issue Sprint Status Change].[Future => Active],
  [Time].CurrentHierarchy.Levels('Day').DateMember(
    [Sprint].CurrentMember.get('Start date')
  )
)

There is, however, a limitation for this formula for sub-tasks. Currently the Jira API does not provide a valid sprint history for sub-tasks: https://jira.atlassian.com/browse/JSWSERVER-11459

The workaround for this problem is to check if the parent of the sub-task has the needed relation to sprint and then show the remaining estimate on the sprint start date. The formula working with the Issue dimension Issue level and Sprint dimension could be like this:

CASE WHEN
 [Measures].[Issue type] Matches "Sub-task|Test task|Data task" --list your sub-task issue types
 AND
 ([Measures].[Issues history],
 [Time].[Day].DateMember([Measures].[Sprint start date]),
  [Issue].[Issue].GetMemberByKey(
   [Measures].[Issue parent key] 
  ))>0
THEN
  NonZero(([Measures].[Remaining estimated hours history],
  [Time].[Day].DateMember([Measures].[Sprint start date]),
  [Sprint].DefaultMember
   ))
END

Kindly,
Janis, eazyBI support

3

Hi,
I tried to use both of the examples here -
I get empty results for the second one… this is what I did
CASE WHEN
[Measures].[Issue type] Matches “Sub-Dev|Sub-DB|Sub-Design|Sub-QA|Sub-IT|Sub-Security” --list your sub-task issue types
AND
([Measures].[Issues history],
[Time].[Day].DateMember([Measures].[Sprint start date]),
[Issue].[Issue].GetMemberByKey(
[Measures].[Issue parent key]
))>0
THEN
NonZero(([Measures].[Remaining estimated hours history],
[Time].[Day].DateMember([Measures].[Sprint start date]),
[Sprint].DefaultMember
))
END

and I actually do get results for the first option - can you explain again the issue here? it means that the results I get my not be updated?
Thanks