Hello EazyBI Community,
I am trying to create a report in EazyBI where I need each resolved ticket to be counted in the quarter it was created, regardless of when it was actually resolved. I’ve attempted several MDX formulas, but none have succeeded in attributing the resolved tickets back to their creation quarter correctly.
Here is what I’m trying to achieve:
I want a report where each ticket that is resolved is counted in the quarter in which it was originally created. For example, if a ticket was created in Q2 2023 and resolved in Q2 2024, it should still be counted under Q2 2023.
Could anyone please help me correct the MDX formula or suggest an alternative approach to achieve this requirement? I am looking for the resolved tickets to be attributed correctly to the quarter of their creation for accurate reporting.
Thank you in advance for your help and suggestions!
Hello @Mohssine_ZAHRI,
Thanks for posting your question!
Overall, you are looking in the right direction, just a few adjustments:
- create a new calculated formula that will return the number of Issues created if there is a resolution date:
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Issues created]>0 AND
NOT IsEmpty([Measures].[Issue resolution date])
),
[Measures].[Issues created]
)
-
pick this new calculated measure for your report, then left-click on the header and choose “Filter rows” → “>” and set a rule to only show results if the new calculated measure is > 0.
-
In my example below, you can see that I am filtering by Q2 2023 in the Page filter, and I get results for all the issues created in Q2 2023 (even if their resolution date is in a different quarter).
As the filtering now is done in formula by the measure “[Measures].[Issues created]>0” then this newly created measure is linked to the time dimension by the issue creation date. And filtering by issue property “NOT IsEmpty([Measures].[Issue resolution date])” doesn’t affect how the measure is linked to time
If needed, you can also later remove the “new calculated measure” column from your report, and the selection will still apply to your results.
I hope this helps!
Best,
Marita // support@eazybi.com
