How determine if an issue is "preexisting" based on affected versions and custom fields

[clarechawke]

Hello

I would like to generate a report that will show if an issue existed in a previous release.

We have a custom field in Jira, “Reported In Version” which is populated with the version that an bug was found in. During testing, the “affects version” is updated with all the versions that the bug is found in.
For examples

• Bug X: Reported in Version: v1.3
  Affects Version: v1.1, v1.2, v1.3
• Bug Y: Reported in Version: v1.3
  Affects Version: v1.3

I want to generate a report which will show all the Bugs that were “Reported in Version” 1.3, and filter out the ones that were pre-existing. So in the example above, Bug Y will be listed and Bug X will be filtered out, as it existed in older releases.

I created the report below and defined a calculated measure “Oldest affected version release date” as follows which looks at all the “affects versions” and find the oldest date:

Tail(
Order(Filter(
DescendantsSet([Affects Version].CurrentHierarchyMember,[Affects Version].[Version]),
Not IsEmpty([Affects Version].CurrentHierarchyMember.Get('Release date'))
AND
[Measures].[Issues created] > 0
), [Affects Version].CurrentHierarchyMember.Get('Release date'), BDESC
)).Item(0).Get('Release date')

This works but I’m unable to use this measure, or any others, to determine which affected versions are older than the “reported in version”. Because “Reported in version” don’t have an associated release date.
Any suggestions on how I can map the current “Reported in version” name to a version date, or somehow determine that it’s older or newer than the “Oldest affected version release date”

Thanks you

[roberts.cacus]

Hi @clarechawke,

Suppose “Reported in Version” is a version picker field you intend to use in the report pages and the Issue dimension in the report rows. In that case, I recommend a calculated measure that will evaluate the currently selected “Reported in Version” value and compare it to each issue Affected version. If the “Reported in Version” matches the first “Affected versions”, the calculated measure formula below will return 1 and empty if not:

IIf(
  [Reported in Version].CurrentMember.GetString('KEY')
  =
  [Affects Version].[Version].GetMemberNameByKey(
    ExtractString(
      [Issue].CurrentHierarchyMember.get('Affects version IDs'),
      '^([^,]+)',
      1
    )
  ),
  1,
  NULL
)

See the expected report results below:

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

Best,
Roberts // support@eazybi.com