Hi Community,
In Measures - User defined I have custom measure “Issues overdue”:
Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issues due] > 0 AND
(
isEmpty([Measures].[Issue due date])
OR
DateDiffDays(DateParse([Measures].[Issue due date]), Now()) > 0
)
)
)
This MDX takes a very long time and if added additional filter by ticket status it will break by timeout.
Is it possible to optimise this measure?
Hi @andrey.mayskiy ,
you can try to use formula “Issues due not in time” from this demo account report: Issues due and overdue - Issues - Jira Demo - eazyBI
CASE WHEN
DateAfterPeriodEnd("Today", [Time].CurrentHierarchyMember)
OR
DateInPeriod("Today", [Time].CurrentHierarchyMember)
THEN
NonZero(SUM(
Filter(
-- filters issues with due dates
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.get('Due date'),
[Time].CurrentHierarchyMember)
AND
(
-- if resolution date is empty then compares due date with now
(IsEmpty([Issue].CurrentHierarchyMember.get('Resolved at')) AND
DateDiffDays([Issue].CurrentHierarchyMember.get('Due date'), Now()) > 0)
OR
-- if resolution date is NOT empty then compares due date with resolution date
(NOT IsEmpty([Issue].CurrentHierarchyMember.get('Resolved at')) AND
DateDiffDays(DateWithoutTime([Issue].CurrentHierarchyMember.get('Due date')),
DateWithoutTime([Issue].CurrentHierarchyMember.get('Resolved at'))) > 0)
)
),
[Measures].[Issues with due date]
))
END
Kind regards,
Gerda // support@eazyBI.com
Hi @gerda.grantina, thanks for your answer, we will try that!
1 Like
Hi @gerda.grantina!
Is it possible to use non operational report in this case? As we need to drill to issues with due date, thanks in advance!