Need to Exclude Issues with a Resolution of Unresolved

Questions & Answers
1

I’m creating a calculated measure for all issues that are or were once In Progress or At Risk. I’ve had difficulty putting this together and finally have it at a point that it is calculating this correctly for each month with the exception of September. This is due to the fact that a large quantity of issues were uploaded into the JIRA project on 9/25/2020 as Completed for history purposes only therefore these issues were never technically In Progress or At Risk but they are incorrectly being counted for September. This that were uploaded would then have a Resolution of Unresolved so I thought to enhance the measure with [Resolution].CurrentMember.Name = ‘Completed’

This did help with the month of September but all months going forward no longer include an issue if it was Completed during that month. How can I fit in an exception for any issues that have a Resolution of Unresolved?

CASE WHEN
DateCompare(
[Time].CurrentHierarchyMember.StartDate,
now()
) < 0
THEN
NonZero(Sum(Filter(
Descendants([Transition Status].CurrentHierarchyMember,[Transition Status].[Transition Status]),
NOT IsEmpty([Measures].[Issues history])
),
CASE
WHEN [Transition Status].CurrentHierarchyMember.Name = “In Progress” OR [Transition Status].CurrentHierarchyMember.Name = “At Risk” AND [Measures].[Issues history] > 0
THEN
[Measures].[Resource #1 % Allocated history]+
[Measures].[Resource #2 % Allocated history]+
[Measures].[Resource #3 % Allocated history]+
[Measures].[Resource #4 % Allocated history]+
[Measures].[Resource #5 % Allocated history]+
[Measures].[Resource #6 % Allocated history]+
[Measures].[Resource #7 % Allocated history]+
[Measures].[Resource #8 % Allocated history]+
[Measures].[Resource #9 % Allocated history]+
[Measures].[Resource #10 % Allocated history]

WHEN [Transition Status].CurrentHierarchyMember.Name = “Completed” AND [Measures].[Issues history] > 0 AND [Resolution].CurrentMember.Name = “Completed”
THEN
[Measures].[Resource #1 % Allocated change]+
[Measures].[Resource #2 % Allocated change]+
[Measures].[Resource #3 % Allocated change]+
[Measures].[Resource #4 % Allocated change]+
[Measures].[Resource #5 % Allocated change]+
[Measures].[Resource #6 % Allocated change]+
[Measures].[Resource #7 % Allocated change]+
[Measures].[Resource #8 % Allocated change]+
[Measures].[Resource #9 % Allocated change]+
[Measures].[Resource #10 % Allocated change]

END
)) / 100 /
CASE
WHEN [Application].CurrentMember.Name MATCHES “A&C” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 2
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
END

2

Hi @Alyssa_A

eazyBI imports changes for few dimensions, like “Priority”, “Issue type” and “Resolution”
For those, you could see a historical number of issues when using “Time” dimension in report, for other dimensions without history imported eazyBI would return the current value of the field regardless of “Time”.
So, I believe you could use the history of “Resolution” in your calculated measure.
Try something like the code below.

Martins / eazyBI team

CASE WHEN
DateCompare(
[Time].CurrentHierarchyMember.StartDate,
now()
) < 0
THEN
NonZero(Sum(Filter(
Descendants([Transition Status].CurrentHierarchyMember,[Transition Status].[Transition Status]),
(
[Measures].[Issues history],
[Resolution].[Completed]
)>0
),
CASE
WHEN [Transition Status].CurrentHierarchyMember.Name = "In Progress" OR 
[Transition Status].CurrentHierarchyMember.Name = "At Risk" 
THEN
[Measures].[Resource #1 % Allocated history]+
[Measures].[Resource #2 % Allocated history]+
[Measures].[Resource #3 % Allocated history]+
[Measures].[Resource #4 % Allocated history]+
[Measures].[Resource #5 % Allocated history]+
[Measures].[Resource #6 % Allocated history]+
[Measures].[Resource #7 % Allocated history]+
[Measures].[Resource #8 % Allocated history]+
[Measures].[Resource #9 % Allocated history]+
[Measures].[Resource #10 % Allocated history]

WHEN [Transition Status].CurrentHierarchyMember.Name = "Completed" AND 
(
[Measures].[Issues history],
[Resolution].[Completed]
) > 0 
THEN
[Measures].[Resource #1 % Allocated change]+
[Measures].[Resource #2 % Allocated change]+
[Measures].[Resource #3 % Allocated change]+
[Measures].[Resource #4 % Allocated change]+
[Measures].[Resource #5 % Allocated change]+
[Measures].[Resource #6 % Allocated change]+
[Measures].[Resource #7 % Allocated change]+
[Measures].[Resource #8 % Allocated change]+
[Measures].[Resource #9 % Allocated change]+
[Measures].[Resource #10 % Allocated change]

END
)) / 100 /
CASE
WHEN [Application].CurrentMember.Name MATCHES "A&C" THEN 3
WHEN [Application].CurrentMember.Name MATCHES "Automation" THEN 4
WHEN [Application].CurrentMember.Name MATCHES "DW" THEN 4
WHEN [Application].CurrentMember.Name MATCHES "HIE" THEN 3
WHEN [Application].CurrentMember.Name MATCHES "HIX" THEN 7
WHEN [Application].CurrentMember.Name MATCHES "MA21" THEN 4
WHEN [Application].CurrentMember.Name MATCHES "MH Sub-Systems" THEN 2
WHEN [Application].CurrentMember.Name MATCHES "MMIS" THEN 10
END
END
1 Like