Unresolved Issues from issues created within each month

Questions & Answers
1

Hello, I am trying to create a table to show how many tickets were created within each month and how many of those created are unresolved (not a rolling balance). I have used the calculated member below and it shows the outstanding totals but not how many from each month are still open. We do not currently utilize sprints or story points and some items do not have due dates on them. Any help would be appreciated!

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
2

Hello @KatelynSolis,

Thanks for posting your question!

To count the Issues that were created in a specific month but not resolved in the same month, you can create the following calculated measure:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- check if issue was created in selected period
    DateInPeriod(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember
    )
    AND
    (
      -- issue is not resolved at all
      IsEmpty([Measures].[Issue resolution date])
      OR
      -- or was resolved in a different period than created
      NOT DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
      )
    )
  ),
  [Measures].[Issues created]
)

You can also check out our “Issues created and resolved in period %” sample report. It looks at your question from a different angle, showing how many issues were resolved during that same time period.

I hope this helps!

Best,
Marita // support@eazybi.com