Count of Tickets Resolved Monthly v Created in the Current Year

Hi Team,

  1. I am looking to create a report where I want to filter the tickets which were created this year (2023) and have been resolved (monthly basis).

For example: A ticket created on 4 January 2023 but was resolved on 19 March 2023. So, I should see this tickets count in the month of March.
Another Example: A ticket created on 22 December 2022 and resolved on February 22, 2023. This ticket should not be included in count as it is not created in the current year.

As of now I am able to achieve the count of Tickets created and resolved in the same month using the below calculated member formula but, this misses out on tickets which were created and then resolved in different months.

SUM(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    DateInperiod(
      [Measures].[Issue created date],
      [Time].CurrenthierarchyMember
    )
    AND
    DateInperiod(
      [Measures].[Issue resolution date],
      [Time].CurrenthierarchyMember
    )
    AND
    [Measures].[Issues resolved]>0
  ),
  CASE WHEN
  [Measures].[Issues created]>0
  THEN
  1
  END
)
  1. I also want to have a column of the average resolution time per month for the same criteria

support@eazyBI.com, requesting your assistance on this.

Hi @ajinder_singh,

The structure is good for the calcaution; here are a few modifications you might want to add

  1. Use [Time].[Year].CurrentDateMember to check if the issue was created in the current Year. See also documentation on:

  2. Use distinct count measures “Issue resolved count” to sum up distinct resolved issues. This would replace the filter criteria [Measures].[Issues resolved]>0 and condition CASE WHEN.

The updated expression might look like this:

Sum(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    --issue created in the current Year
    DateInperiod(
      [Measures].[Issue created date],
      [Time].[Year].CurrentDateMember
    )
    AND
    --and issue resolved in the selected Time period
    DateInperiod(
      [Measures].[Issue resolution date],
      [Time].CurrenthierarchyMember
    )
  ),
  --sum up resolved issue count
  [Measures].[Issues resolved count]
)

Best,
Zane / support@eazyBI.com