I’m trying to generate a report that is being difficult to me. I want to generate a list of issues that was in a determined status for more than X days. I.e. in the last 3 months (issues opened for more than 15 days in August, in September and in October).
I can’t see an example like this and I’m also unable to build it by myself.
Actually I have built this example but it is even more weird because the result is “YYYY-MM-DD” instead of a number!
If you’re seeing “YYYY-MM-DD” in your cell rather than the value you’re seeking, check to see if the formatting option of your custom calculated member is set to “Default”. Instead, try another option like Integer and see if that fixes the issue.
That being said, I think you’d want to use the [Measures].[Days in transition status] calculated measure. NOTE: this will only calculate if the issue has been moved out of that status. So if you have a workflow that is To Do --> In Progress --> Done, then if an issue is moved from To Do to In Progress and stays In Progress for 20 days, that 20 days mark won’t be calculated until the issue is transitioned out of In Progress.
Another way of handling that would then be to look for the exceptions. You can verify that [Measures].[Transitions to status last date] (In Progress) is greater than [Measures].[Transitions from status last date]. This way you know that the issue moved into the In Progess status in this example but hasn’t moved out yet, thus you can use that as an exception handler and add it to the calculation created by [Measures].[Days in transition status] calculated measure to get the appropriate time that you’re looking for.
