I’m trying to count overdue defects by a customized “severity”, and for each severity level there is different time threshold. I did some research, but it doesn’t work. following is my code, any help will be appreciated. Thanks!
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
DateInPeriod(
([Measures].[Issue created date]),
[Time].CurrentHierarchyMember)
),
CASE WHEN
DateDiffMinutes(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember
) <
CASE [Issue].CurrentHierarchyMember.GetString(‘Severity’)
WHEN “H” THEN 5
WHEN “M” THEN 7
WHEN “L” THEN 60
ELSE 600
END
THEN [Measures].[Issues created]
END
)
Can you provide a visual example of what you try to achieve?
Can you also specify if you target only the overdue defects still open or the report should include the one that were overdue but closed/done in the meantime?
The settings are as below:
Pages: Project, Status (I choose all the non-closed status from the Status drop-down list )
Rows: severity (Customized)
Columns: Measures (Issues Created, overduebyseverity)
A likely reason why the formula does not work is that the report does not use the Time dimension, and the formula cannot calculate the duration between issue creation data and [Time].CurrenthierarchyMember. The formula also is not precisely matching the syntax required for the Sum function.
There can be several ways to define overdue issues, e.g., unresolved issues exceeding the number of days since the creation till now:
NonZero(Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
IsEmpty([Measures].[Issue resolution date])
),
CASE WHEN
DateDiffDays([Measures].[Issue created date],Now())>
CASE [Issue].CurrentMember.GetString('Severity')
WHEN "H" THEN 5
WHEN "M" THEN 7
WHEN "L" THEN 60
ELSE 600
END
THEN
[Measures].[Issues created]
END
))