Filter by Priority OR creation date more than 6 months

[rdelabarrera]

Hello, I would like to create a filter that shows the issues that:

• Are Blocer or Ciritical, it doesn’t matter the creation date.
• Were created more than 6 months ago, It doesn’t matter the Priority.

Something like this JQL: “(priority in (Blocker, Critical) OR createdDate < “2021/06/01”)” For this example the date is hardcoded.

Any idea on how the filter should look like?

[janis.plume]

Hi,

The most straightforward solution for such a use case would require Filter and Descendants functions in the custom calculation. You can reproduce the JQL query in the filter condition:

NonZero(Count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  [Measures].[Issue priority] MATCHES "Blocker|Critical"
  AND
  DateCompare([Measures].[Issue created date],'180 days ago')<0
  AND
  ([Measures].[Issues due],
  [Time].CurrentHierarchy.DefaultMember)>0
  )
))

This code uses dynamic date filtering for finding the issues created more than 180 days ago. The use of the Issues due measure limits the issues to the unresolved ones. You can also replace the measure with the Issues created to show the resolved.

This formula can have performance limitations, so there is a more efficient workaround using the Age interval dimension if the unresolved (due) issues are needed.

1. Selec the import option to create the Age interval dimension and configure the Age interval dimension in two intervals: less than 180 days of age and more than 180 days of age:
   

   

2. Now, you can refer to the interval and create a formula for finding the issues according to your condition:

([Measures].[Issues due],
[Priority].[Some priorities])
+
([Measures].[Issues due],
[Age interval].[180 - ...]
)
-
([Measures].[Issues due],
[Priority].[Some priorities],
[Age interval].[180 - ...])

Here, “Some priorities” is a calculated member in the Priority dimension aggregating the needed priorities.

Both formulas should work identically, but the latter is much more efficient:

Kindly,
Janis, eazyBI support

[rdelabarrera]

Thanks a lot for your guidanse Janis. On my original script there was a single line missing and you helped me with that one. My final filter ends up like this:

Aggregate({
Filter(
[Issue].[Issue].Members,
(
[Measures].[Issue priority] MATCHES (“Blocker|Critical”) OR
DateCompare([Measures].[Issue created date],‘180 days ago’)<0
)
)
})

The DateCompare function was the key here