Release Date from Fix Version on top level member

[wojo3]

New to eazyBI…
I would like to display the Fix Version Start Date and Release Date as Vertical lines in a timeline chart from a specific Jira Project.
I am having trouble creating the Report specific calculated measures.
In my case, I am pulling data from 4 Jira Projects, one of which is the top level ART. There is a naming convention for the Fix Versions that are to be used - XXX PI23.1, XXX PI23.2, XXX PI23.3, etc.
I want to be able to display, automatically, the Start and Release dates of the current Fix Version that I am in.
Example: If Jira Projects are: TopLevel, sub1, sub2, sub3. Get Start and Release Dates from TopLevel where Fix Version Name starts with “XXX” and Release Date >= Today >= Start Date

[roberts.cacus]

Hi @wojo3,

Do the identical versions from all four Projects have different start and release dates? If not, I recommend selecting the desired version from the Fix Version “By name” hierarchy. The “By name” hierarchy groups all versions by their name from all imported Projects. Then you can retrieve the start and release date with the calculated measure formula below:

CASE WHEN [Fix Version.By name].CurrentMember.Level.Name = 'Project'
THEN
[Fix Version.By name].CurrentMember.Get('Start date')
ELSE
[Fix Version.By name].CurrentMember.FirstChild.Item(0).Get('Start date')
END

For the release date, replace 'Start date' with 'Release date'. After that, you can define a new calculated measure to display the start and release dates as vertical lines:

Case When
  DateInPeriod(
    [Measures].[Start date],
    [Time].CurrentHierarchyMember
  )
Then
  [Fix Version].CurrentHierarchyMember.Name || ' - ' 
  || + Format([Measures].[Start date], 'Medium Date')
WHEN
  DateInPeriod(
    [Measures].[Release date],
    [Time].CurrentHierarchyMember
  )
THEN
  [Fix Version].CurrentHierarchyMember.Name || ' - ' 
  || + Format([Measures].[Release date], 'Medium Date')
END

See the screenshot below for expected results:

Please look at our documentation page for more information on defining calculated measures - ​Calculated measures and members.

Best,
Roberts // support@eazybi.com

[wojo3]

Thanks for the reply @roberts.cacus!
I am digesting what you wrote, in the meantime, I should add some clarification.

One issue is that the Fix Versions are not named exactly the same. The Fix Version naming would be as follows for an ART and 3 teams:

“EMP ART PI23.2”
“EMP TEAM3 PI23.2”
“EMP TEAM2 PI23.2”
“EMP TEAM1 PI23.2”

What if we assume all start/end dates are the same, and I just focus on the top level project, is there a way to say:

For Project X that has multiple fix versions, get me the start and release date for any fix version where the fix version name begins with “EMP ART” and Today <= Release date AND Today is >= Start date

[roberts.cacus]

Hi @wojo3,

If the versions don’t have the same name, I recommend defining a calculated member for each set of versions, aggregating the versions by a regular expression match. The Fix Version dimension calculated member formula could look similar to the one below:

Aggregate(
  Filter(
    [Fix Version].[Version].Members,
    [Fix Version].CurrentMember.Name MATCHES '^EMP .* PI23\.2$'
  )
)

You can then select this calculated member in the report pages.

The calculated member aggregates Fix Version dimension members from the default hierarchy. That means the start and release date calculation needs to change as well. The start date calculation will look like the one below:

CASE WHEN [Fix Version].CurrentHierarchyMember.Level.Name = 'Version'
THEN
[Fix Version].CurrentHierarchyMember.Get('Start date')
ELSE
ChildrenSet([Fix Version].CurrentMember).Item(0).Get('Start date')
END

The vertical line calculated measure should work after the start and release date calculations change.

Best,
Roberts // support@eazybi.com

[wojo3]

Just wanted to follow up that I have the solution now to what I wanted to do.
Using the Fix Version dimension in Pages, the code below is a calculated member formula that allows me to report on items in Fix Versions where today’s date is between the start and end dates of the Fix Versions.
In addition, it only looks at specific Fix Versions based on a filter on the Fix Version name.

Aggregate(
  Order(
    Except(
      Filter(
        [Fix Version].[Version].Members,
        DateBetween(
          Now(),
          [Fix Version].CurrentMember.Get('Start date'),
          [Fix Version].CurrentMember.Get('Release date')
        )
      ),
      Filter(
      [Fix Version].[Version].Members,
      [Fix Version].CurrentHierarchyMember.name not MATCHES ".*PI.*"
      )
    ),
  [Fix Version].CurrentMember.Get('Release date'), BASC
))```

[roberts.cacus]

Hi @wojo3,

I am happy you found the solution for your requirement. While the calculated member formula works as expected, it iterates twice through the same set of versions. For optimal performance, you don’t need the Except() function and two sets of “Version” level members. Instead, you can filter the set with both conditions and separate them with AND:

Aggregate(
  Order(
    Filter(
      [Fix Version].[Version].Members,
      DateBetween(
        Now(),
        [Fix Version].CurrentMember.Get('Start date'),
        [Fix Version].CurrentMember.Get('Release date')
      )
      AND
      [Fix Version].CurrentHierarchyMember.name MATCHES ".*PI.*"
    ),
    [Fix Version].CurrentMember.Get('Release date'), BASC
  )
)

Best,
Roberts // support@eazybi.com