Hi,
I would like to create a report showing the average number of status changes of closed tickets by month. I found this post from 2020 which is heading towards a solution, but it only counts the status changes in the given month, not the status changes in previous months. I am - again - at a loss on how to move forward.
Hi, @Maiko
Welcome to the eazyBI community.
When the Time dimension is selected for the given month, and you are looking for some aspects for “all times” - please use the [Time].DefaultContext to ignore the Time set in the Page filters.
NonZero(
Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
(
[Measures].[Issues closed],
[Time].CurrentMember
) > 0
),
(
[Measures].[Transitions to status],
[Time].CurrentHierarchy.DefaultMember
)
)
)
This formula does the following:
- Filters issues using
[Measures].[Issues closed]
with[Time].CurrentMember
to select only issues closed in the current month (as selected in pages). - For the filtered issues, calculate the average number of transitions using
[Measures].[Transitions to status]
with[Time].CurrentHierarchy.DefaultMember
to count all transitions regardless of when they occurred.
Kindly,
Ilze
1 Like