I’m trying to create a report for the average time my team is responsible for an Issue, per Component and per month. This corresponds to the time the Issue spends in two specific Status, since the time spent on other Status depends on other teams.
I was able to do this using the included [Average workdays in transition status] Measure, however we only work from 9am to 5pm and if someone moves an Issue to one of our Status at 10pm and we resolve it by 10am the next day it will count as taking 0.5 workdays, while we only took an hour to solve it. This can happen frequently as some teams work on completely different time zones.
To solve this I’ve been trying to create an Average workhours in transition status Measure.
So far I’ve made this:
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
Not IsEmpty([Measures].[Transition to status first date])
AND Not IsEmpty([Measures].[Transition from status last date])
),
DateDiffWorkHours(
[Measures].[Transition to status first date],
[Measures].[Transition from status last date],
'default',
'9-17'
)
)
However this runs into a couple of issues.
One is that Issues may pass by the same Status multiple times. If it wasn’t completed properly the Issue will be sent back to the previous status with comments. This doesn’t get tracked properly, since my calculated measure only looks at the first and last transition to each status.
The second issue is that the issues may be started in a month and end in a different month. When filtering by month I realized the [Transition to status first date] and [Transition from status last date] measures, don’t show any dates outside that month. So my measure isn’t taking into account issues spanning across two months properly.
Any tips on how to do this?
I’d like to peek at how the [Workdays in transition status] Measure is implemented in MDX, since I probably have to do something similar, but that’s not a measure that I can show.
We do have a backlog feature request to implement configurable work hours calculations for time spent in status or transition, but that is a potential rabbit hole that is still gathering votes and has not reached the development stage yet.
Still, that would have the following pitfalls to address:
festive holidays from import settings are not observed by the script - they should then be added manually to the script and excluded from recorded time
ongoing current time in specific status is not observed - depending on the final calculation used, the data might be correct only for the month that had ended before the last full data import.
multiple transitions within a date would overwrite each other - might require additional coding to merge the time of multiple transitions within the same date
the handling of various time zones differs between DC and Cloud instances - the scripts in the community posts are not explicitly aligning timezones
stale issues have stale data - regular full data re-imports might be required to update time in statuses for the issues that are not being updated for long
script that handles problems 1)-4) might be too complex to succeed within a reasonable timeout limit during the data import process.
An alternative option might be the MDX calculation that inspects each issue and then inspects the days between the initial transition to and the last transition from, for the number of transitions to/from status within the day.
That approach might theoretically work as long as there are no exiting transitions outside business hours.
The calculation might be so complex that it might exceed any reasonable timeout limits on a substantial dataset.
The measure “Workdays in transition status” is pre-calculated from issue changelog records during the data import using scripts.
I have to admit that the perfect solution for your case is yet to be made.