We are wanting to show how many issues are open at one time on EasyBI. We are currently using the “Open Issues” measure to do this,
“CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’ THEN
Cache(
NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
[Measures].[Issues created]
- [Measures].[Issues resolved]
))
+ [Measures].[Issues created]
- [Measures].[Issues resolved]
)
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN NonZero([Measures].[Issues due])
ELSE
– optimized formula for drill through Issue
NonZero(IIF(
DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Created at’),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember),
([Time].CurrentHierarchy.DefaultMember,
[Measures].[Issues created]),
0
))
END”
but the value it returns is never accurate, and after drilling through into the issues, its giving us some Resolved and Closed issues.
Changing the filter to not include Resolved/Closed issues just breaks everything in the report.
Is there are way I can just show how many issues are/were open on each day on the report?
“Open issues” measure will show the count of issues that were in an unresolved status at the end of the selected period.
In context with Time, this means that when you are filtering data by December 2023, you will see all issues that were open on the 31st of December 2023 (even if the issue is closed now, in January 2024).
In my example, here below, I have an issue DA-351 that has been created in December 2022 and was in status “To Do” until today, when I closed it:
If I create a report and filter by December 2023 results, I would still get Issue DA-351 in my results even if it has been closed in January 2024. This is because Time dimension is checking issue DA-351 against status “Open” in December 2023, thus the result is actually correct.
I also suggest to apply the filter for “Open issues” measure: left-click on the “Open Issues” column header, choose “Filter rows” - “>” (bigger than), and create a rule to only return results that are >0. This will allow you to hide the rows where there are no values returned.
