Open, Closed, Overdue and Unrefined issues under all epics in an Initiative

[janedoe2]

@nauris.malitis

You’ve been so helpful in getting the exact data I need that we’re getting more requests for more views!

If you recall Displaying specific issue types in an epic where the epic has a specific custom field , you helped me establish a view where a ticket type “Milestone” exists based on the parent “Program” field in the parent at two hierarchies - one at the epic, and one at the initiative.

I can’t get accurate measurements for how many of these “Milestone” tickets exist that are Open (status not closed), Overdue (status not closed and past todays date) Unrefined (no Target end date assigned).

The report should look like this

Initiative (with a + to expand epics)|Milestone Closed|Milestone Open|Milestone Overdue|Milestone Unrefined

The data is set up like this and I want to report on the children at the Initiative Level. Is this possible?

Initiative
  Epic 1
    Deliverable1
    Deliverable2
  Epic 2
     Deliverable1
     Deliverables 3-15.

Measures Below:

Here are my current calculated measures:
Deliverables Unrefined

– Milestones Unrefined (Final, with correct context)
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
– Pre-filter for non-closed Milestones for performance
[Issue].CurrentHierarchyMember.GetString(‘Issue Type’) = ‘Milestone’
AND
[Issue].CurrentHierarchyMember.GetString(‘Status’) <> ‘Closed’
),
– Check if the Target End is empty for EACH descendant issue
CASE WHEN
IsEmpty(
([Measures].[Issue Target end], [Issue].CurrentHierarchyMember)
)
THEN 1
ELSE 0
END
)

Deliverable Overdue:

– Milestones Overdue (Final, with correct context)
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
– Pre-filter for non-closed Milestones for performance
[Issue].CurrentHierarchyMember.GetString(‘Issue Type’) = ‘Milestone’
AND
[Issue].CurrentHierarchyMember.GetString(‘Status’) <> ‘Closed’
),
– Check the Target End for EACH descendant issue
CASE WHEN
DateCompare(
([Measures].[Issue Target end], [Issue].CurrentHierarchyMember),
“today”
) < 0
THEN 1
ELSE 0
END
)

Deliverable Open
– Milestones Open (Final, Most Efficient)
(
[Measures].[Issues with Target end],
[Issue Type].[Milestone],
[Resolution].[(unresolved)]
)

[Measures].[Milestones Overdue]

[Elita.Kalane]

Hi @janedoe2

Thanks for posting your question!

Here are my recommendations:

Step 1

In your requirements, you mentioned that Open for you means not in status Closed; however, in some of your measures, you use a reference “[Resolution].[(unresolved)]”. I am not sure what approach you would like to take, so I will show an example with Status not in closed. If that is not what you prefer, you can change the parts where I refer to the Status dimension and replace them with the Resolution dimension.

In Status dimension define a new calculated member that will aggregate all statuses except for Closed (this will make it much easier to count the “open issues”). If you’ve never defined a calculated member, please take a look at the documentation page here - Calculated members in other dimensions

For example purposes, let’s assume you name this member “Except closed”

Aggregate(
 Except(
 [Status].[Status].Members, 
 [Status].[Closed] 
 )
)

Step 2

Measure for Open Milestones

When the calculated member in Status dimension is created, you can define a new calculated measure in the Measures dimension with formla below. This will count all milestones below the visible Initiative that are open (not in status Closed)

(
  [Measures].[Issues created],
  [Issue Type].[Milestone],
  [Status].[Except closed]
)

Step 3
Measure for Deliverables Unrefined

([Measures].[Issues created],
[Issue Type].[Milestone],
[Status].[Except closed])
-
([Measures].[Issues with Target end],
[Issue Type].[Milestone],
[Status].[Except closed])

Step 4,
Measure for Overdue

NonZero(Count(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- Filter for milestone issue type
    [Measures].[Issue type] MATCHES 'Milestone'
    AND
    -- Check if the issue has a due date
    NOT IsEmpty([Measures].[Issue Target end])
    AND
    -- Check that the due date is before today (overdue)
    DateCompare(
      [Measures].[Issue Target end],
      'Today'
    ) < 0
    AND
    -- Check that the issue is not resolved
    ([Measures].[Issues created],
    [Status].[Except closed]) >0
  )
))

Let me know if this works as expected!
​Best regards,

Elita from support@eazybi.com

[janedoe2]

Thank you - this worked perfectly, and performs much better than the solution I had.