Linking external issues to Jira sprints

Questions & Answers
1

I imported external data via data import. Is there a way to connect them to other data in the same cube via date?

What I have in my cube:

  • Issues with a resolution date (not from Jira)
  • List of my Jira Sprints (containing name, start and end date)

What I want:

  • Link these issues to Jira Sprint via the resolution date.

What I tried:

[Measures].[Tickets Resolved Count] is calculated while importing the data with the Date count measure option.

With Sprints as rows:

First try:

NonZero(SUM(
Filter(
Descendants([JiraSprint].CurrentMember, [JiraSprint].[JiraSprint]),
DateInPeriod(
[JiraSprint].CurrentMember.GetDate(‘Sprint Start’),
[Time].CurrentHierarchyMember)),
[Measures].[Tickets Resolved Count]
))

Second try:

NonZero(Sum(
Filter(
Descendants([Tickets].CurrentMember,[Tickets].[Ticket]),
DateBetween(
[Tickets].CurrentMember.GetDate(‘Resolution Date’),
[JiraSprint].CurrentHierarchyMember.GetDate(‘Sprint Start’),
[JiraSprint].CurrentHierarchyMember.GetDate(‘Sprint End’))
),
[Measures].[Tickets Resolved Count]
))

Third try:

With Tickets as rows:

CASE WHEN
DateBetween([Tickets].CurrentHierarchyMember.GetDate(‘Resolution Date’),
[Measures].[JiraSprint Sprint Start],
[Measures].[JiraSprint Sprint End]
)
THEN [Measures].[Tickets Resolved Count]
END

Is there a way to do this within eazyBI without adding this information to my (very long) csv file with Excel… :scream:

2

Hi @sven

Thanks for the elaborate description!

It seems that this code should do the trick if you have the individual Tickets in Rows:

Filter(
  Descendants([JiraSprint].CurrentMember,[JiraSprint].[JiraSprint]),
  DateBetween(
    [Tickets].CurrentHierarchyMember.GetDate('Resolution Date'),
    [JiraSprint].CurrentHierarchyMember.GetDate('Sprint Start'),
    [JiraSprint].CurrentHierarchyMember.GetDate('Sprint End')
  )
).Item(0).Name

This will, for every Ticket, iterate through all Sprints and find which Sprint dates fit with the Resolution date of the Ticket. This formula assumes that there will be only one sprint after the filtering and it will retrieve this Item and return the Name of this Sprint.

Let me know if this works as expected!
Best regards,
Nauris / eazyBI support