Count of Open Issues across time member not just end date

Questions & Answers
1

I’ve searched for related threads, but I cannot seem to find a straightforward answer to how to count for instance Open Issues across a time member. Let’s say I want to count how many open issues existed across a month in a 3 month report. The existing Open Issues measure counts what was open at the end of that month, but I’m trying to count the load for a team, including issues that may have been created then closed within a couple of days.

I assume this will rely on Issue History, but even that seems to relate to the last day in a Time member.

2

Hi Jordan,
​
You are right, the general measures all relate to the end of the last date of the Time dimension member.​
​
The issues being worked on during a time period have several possibly overlapping conditions. Therefore, the precise ​calculation requires iteration through the Issue dimension and checking each issue for compliance with any of the conditions.
The expression might be as follows.​
​

Sum(
 Filter(
   DescendantsSet(
    [Issue].CurrentHierarchyMember,
    [Issue].[Issue]),
--created during the period
 ([Measures].[Issues created]
 +
--reopened during period
([Measures].[Transitions to issues count],
[Transition Field].[Resolution],
[Resolution].[(unresolved)])
+
--open at beginning
([Measures].[Issues history],
[Resolution].[(unresolved)],
-- [Time].CurrentHierarchyMember.PrevMember
--alternative for calculated time
[Time].CurrentHierarchy.Levels(“Day”).DateMember([Time].CurrentHierarchyMember.StartDate).PrevMember))>0
),
--measure for sum
1
)

​
​Depending on the size of your dataset, you might consider a tradeoff performance for precision.
​
​The faster option might be to consider all issues open at the beginning of the time period, then add all created during the period and add those that were re-opened during a period. This would be a much faster option. However, the issues that were created during the period, resolved and then reopened might be counted multiple times.
​
​The expression might be as follows.

--open at beginning
([Time].CurrentHierarchyMember.PrevMember,
[Measures].[Issues history],
[Resolution].[(unresolved)]),
+
--created during period
([Measures].[Issues created],
[Time].CurrentHierarchyMember)
+
--reopened
([Measures].[Transitions to issues count],
[Transition Field].[Resolution],
[Resolution].[(unresolved)],
[Time].CurrentHierarchyMember)

​Regards,
​Oskars / support@eazyBI.com