Derive sprint start date for earliest sprint associated with each issue

[mbarnes]

Hello,

I am trying to compare issue creation date vs “start date of the first sprint the issue was associated with”.

In cases where an issue was in multiple sprints, how can I derive the start date of the earliest sprint?

I can see [Measures].[Issue Sprints] tells me all the sprints an issue was associated with, which seemed a good place to start.

And I found this previous topic on how to derive a sprint start date, which also looked promising.

Derive Start Date of the Sprint from Issue(Story or Sub-Task)

Hello, I am trying to make a report that shows the list of issues . i am able to see the ‘Issue Sprint’ since it is an issue property. however, i would also like to derive the ‘Sprint Start Date’ and the ‘Sprint End Date’ for each of the listed issue (without having to use the Sprint Dimension in the report itself). What i mean is, in the derived measure, i can refer to the [Sprint] dimension in the MDX formula, but not show the sprint itself as dimension on the end report. is this even allow…

However because it uses [Measures].[Issue Sprint], it seems to provide only the start date of the latest sprint associated with an issue, rather than the earliest. This is perfect for issues only in a single sprint, but not for those issues which have been in multiple sprints. I’ve tried to modify the query but so far to no avail.

In theory I think I need to get all sprints associated with an issue, then get all the start dates of those sprints, then filter those dates to retain only the earliest one - or something along those lines… can someone help me?

[roberts.cacus]

Hi @mbarnes,

Try defining a new calculated measure that retrieves the current issue Sprints as the Sprint dimension members. For that, you can use the GetMembersByKeys function. Then you can order the retrieved Sprint dimension members by their start date with the Order() function. Finally, return the name of the earliest member with item(). See the suggested formula below:

Order(
    [Sprint].[Sprint].getMembersByKeys(
      [Issue].CurrentHierarchyMember.get('Sprint IDs')
  ),
  [Sprint].CurrentHierarchyMember.Get('Start date'),
  BASC
).item(0).Name

The result could look similar to the one below:

See more details on the used functions in the eazyBI documentation page - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference.

Best,
Roberts // support@eazybi.com

[mbarnes]

Thank you, this ending with .Get('Start date') instead of .Name was exactly what I needed