Hello everyone, I need help in refining the formula for calculating the number of tasks whose time in the current displayed status is less than 10 minutes.
In my report, I am currently using “Transition status” as rows and the formula for counting looks like this:
Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
Not IsEmpty([Measures].[Issue resolution date])
AND
DateDiffMinutes(
[Measures].[Transition to status first date],
[Measures].[Transition from status first date]
) < 10
AND
DateInPeriod(
([Measures].[Transition from status first date], [Time].CurrentHierarchy.DefaultMember),
[Time].CurrentHierarchyMember
)
),
([Measures].[Transitions from status issues count], [Time].CurrentHierarchy.DefaultMember)
)
The problem is that the task’s lifecycle time can be more than 1 month, and the information about the task’s time in a status is not counted for other then this current month.
I need to refine the formula for the entire task lifecycle, because I know, thats there is more then 50 issues with lifetime in status < 10 miins at this example, and if i added previous month, i can find exactly same issue and it will be counted in this status, but in current this status will be skipped and not counted
Welcome to the eazyBI community, and thanks for sharing the formula and the screenshot of your report. It’s always easier to find the root cause when we can see the screenshots and the formulas used.
When reviewing the report and reading your concern about the returned results, I would appreciate it if you clarified your expectations when you use September as the report filter? You mention you want to track the whole lifecycle, but you also use the Time filter, hence my question regarding the purpose of the Time filter.
I tried to illustrate the behavior of the formula you use in the screenshot below. The filter criteria you use in the formula will interact with the Time dimension if you decide to filter your report in the following way. In your report, the results will be returned if the following criteria is true:
The function DateDiffMinutes To/From status must be less than 10, and will happen only if the transitions to and from status (both) have taken place in the same period - September 2023 or other displayed Time period
the first transition from the “In Progress” status for the issue should have taken place in the selected period - September 2023
My screenshot below shows that if the Time member “September” is used as a filter, my report returns 3 issues. If I disregard the Time filter looking at “All Times” member, the result is 22 issues:
You mentioned there are a lot more than 50 issues that meet your criteria. I suggest to do debugging/troubleshooting in the following way:
Identify at least one of the issues that is not captured by the formula, add the particular issue in Rows and then add these below-marked issue properties in the report (along with Transition status “In progress either in Rows or Pages” and see what dates are returned. Do not add a Time dimension to the report. If one of the dates is not in September, that will be your answer to why the particular issue is not captured by the formula you use.
