Velocity report based on fix version and specific status transition

Questions & Answers
1

Hi again guys

I’m trying to build a velocity report (sum of story points delivered per month), taking into account that:

  • There is no notion of Sprint, so I need to use the release date of the “fix version” associated to the issues.
  • Some issues can have more than 1 fix version associated so I need to count the story points of that issue only once (when the release date of the first fix version took place).
  • There are some issues which don’t have “fix version” but must be considered as delivered (those are special cases that must be handled in that way), so we flag them as “delivered” at the moment they have transitioned to a specific status “DELIVERED”.
  • We cannot rely on status “DONE” or resolution “Done” because the “DELIVERED” status is just an intermediate phase within issue workflow.

NOTES:

  • I know the way to flag “delivered” issues is a little bit twisted and complex, but it’s the way it’s: We cannot change the logic behind, nor create Sprints.
  • Here are the 2 formulas I have tried but with no success (the results are not the expected ones):

FORMULA ATTEMPT #1

Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Issue].CurrentMember.get(‘Story Points’) > 0
AND
– by first date the issue transitioned to status = DELIVERED
DateInPeriod(
[Measures].[Transition to status first date],
[Time].[Year].DefaultMember
)
AND
([Measures].[Transitions to status],
[Transition Status].[DELIVERED],
[Issue Type].CurrentHierarchy.DefaultMember,
[Time].CurrentHierarchy.CurrentMember) > 0),
[Issue].CurrentMember.get(‘Story Points’)
)

FORMULA ATTEMPT #2

Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Issue].CurrentMember.get(‘Story Points’) > 0
AND
[Measures].[Version release date] > 0
AND
– by first date the issue transitioned to status = DELIVERED
DateInPeriod(
[Measures].[Version release date],
[Time].CurrentHierarchy.DefaultMember
)),
[Issue].CurrentMember.get(‘Story Points’)
) +
Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Issue].CurrentMember.get(‘Story Points’) > 0
AND
[Measures].[Version release date] = 0
AND
– by first date the issue transitioned to status = DELIVERED
DateInPeriod(
[Measures].[Transition to status first date],
[Time].CurrentHierarchy.DefaultMember
)
AND
([Measures].[Transitions to status],
[Transition Status].[DELIVERED],
[Issue Type].CurrentHierarchy.DefaultMember,
[Time].CurrentHierarchy.CurrentMember) > 0),
[Issue].CurrentMember.get(‘Story Points’)
)

Can you please help me here?

Thanks in advance for your answer

2

Sorry to push here but is anybody looking at this?

Any help would be really appreciated.

Thanks again!

3

Hi Alvaro,

Yes, I took a look at this and it is quite a complex formula. Here is what I think matches all your defined conditions - sum story points on the date of the first transition to Delivered status or, if there is Fix version, then on it’s release date. See comments inline.

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    -- if issue is created after the current time period
      DateBeforePeriodEnd(
        [Issue].CurrentHierarchyMember.get('Created at'),
        [Time].CurrentHierarchyMember
      ) AND -- and has story points
      NOT IsEmpty([Issue].CurrentMember.get('Story Points'))
    AND
    -- if issue has no Fix version 
    (  ((
        [Fix Version.By name].[(no version)],
        [Measures].[Issues created],
        [Time].CurrentHierarchy.DefaultMember 
       ) > 0 AND
       -- by first date the issue transitioned to status = DELIVERED
       DateInPeriod(
        (
          [Measures].[Transition to status first date],
          [Transition Status].[DELIVERED], 
          [Time].CurrentHierarchy.DefaultMember
        ),
        [Time].CurrentHierarchyMember
      )) -- or Fix Version first item release date
      OR 
      DateInPeriod(
        Filter(
          [Fix Version].[Version].Members,
          ([Measures].[Issues created], [Time].CurrentHierarchy.DefaultMember) > 0 ).Item(0).get('Release date'),
        [Time].CurrentHierarchyMember
      )
    ) -- to match all other contexts of report
    AND ([Measures].[Story Points created], [Time].CurrentHierarchy.DefaultMember) > 0),
  [Issue].CurrentMember.get('Story Points')
)

Lauma / support@eazybi.com

4

Thanks for your answer Lauma!

I will give it a try and I’ll let you know if it works :wink:

Best regards

1 Like