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
Hello @ilze.mezite
After a long sickness I had finally time to implement the solution you proposed. I think I did not describe my aspired report correctly.
What I would like to create is a report showing the average number of transitions to a specific status over the course of the issues’s lifetimes for all tickets closed in a specific month. The x axis shall show the months, the y axis a digit. The bar on a month would mean e. g.: the average amount of lifetime transitions into status ABC of all tickets closed in May 2024 is 8.6
Does this make sense of do I confuse you?