I am creating a report which calculates the average resolution time of a ticket for all tickets that did not spend any time in the “Waiting for external support” status. When placing the individual issues in the Rows section (Dimension “Issue”, hierarchy level Issue), the following formula shows the resolution time per individual ticket, only when the measure expressing the time spent in the status is empty:
SUM(Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
IsEmpty([Measures].[Total days in Waiting for external support (Standard)])
AND
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember
)), DateDiffDays(
[Issue].CurrentHierarchyMember.Get(‘Created at’),
[Issue].CurrentHierarchyMember.Get(‘Resolved at’)
))
However, when trying to ‘zoom out’ and look at the numbers on a monthly, quarterly or yearly basis, by adding the Avg() function and replacing the Issue dimension with the Time dimension in Rows, the numbers don’t add up anymore. How do I resolve this problem?
I am guessing the problem arises due to the fact that I am referring to the current hierarchy level in the MDX above, which causes the wrong numbers to be used for the calculations, but I am afraid I do not know what to try to resolve this problem.
I’ve also noticed that the system is already having difficulties loading the data with my current MDX, giving an error message when trying to load the data for one year or more. So if anyone has any tips on how to avoid this, I’d be interested.
Thanks in advance for any help!