I am using one of our example reports for Average Days in Transition - that works great but I want to exclude the status of Done - so that the report shows all status except Done
NonZero(Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Issues in transition status] >=0),
– for previous periods when issues was in particular status
[Measures].[Days in transition status]
+
– time how long (til now) issue is in this status currently
IIF([Measures].[Issues in transition status] > 0,
DateDiffDays(
([Measures].[Transition to status last date],
[Time].CurrentHierarchy.DefaultMember),
Now()), 0)
))
You can add another condition: NOT [Measures].[Issue status] = “Done”
This will additionally filter only those issues that do not have status “Done”.
Like so:
NonZero(Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Issues in transition status] >=0
AND NOT [Measures].[Issue status] = “Done”),
– for previous periods when issues was in particular status
[Measures].[Days in transition status]
+
– time how long (til now) issue is in this status currently
IIF([Measures].[Issues in transition status] > 0,
DateDiffDays(
([Measures].[Transition to status last date],
[Time].CurrentHierarchy.DefaultMember),
Now()), 0)
))
What @GvidoN wrote is right on track regarding the current status filter!
If you were also looking for historic status filtering, the following formula would first filter out days issue has spent in Done status historically.
Sum(Filter(
Descendants([Transition Status].CurrentMember,
[Transition Status].[Transition Status]),
[Transition Status].CurrentMember.Name <> "Done"),
[Measures].[Days in transition status] +
Sum(Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issue status] <> "Done" AND
[Measures].[Issues history] >=0
),
CASE WHEN [Transition Status].CurrentMember.Name = [Measures].[Issue status]
THEN DateDiffDays([Measures].[Issue status updated date], Now())
END
))
Few more fine-tuning things in the formula above:
It is more efficient to filter Issues first by their properties and then by measure, so I am first filtering the Issues that are not in Done status and only then if they have been in the other statuses.
I am using the ‘Issues history’ measure instead of ‘Issues in transition status’. Since 4.1.0 eazyBI version, the ‘Issues in transition status’ measure has been renamed to ‘Issues history’. Please change back if you are still using an older version of eazyBI.
Hi @lauma.cirule@GvidoN@briantaylor can you please help me here ? I just want to find out the time spent in each status by an issue.
Suppose ex. AAA-101 current status is “Done”. But how many days it spent in “To Do”, “In Progress”, “In Review”, “In QA” like this.
It might be gone in same status again and again 2-3 times.
Thanks @lauma.cirule yes it works perfectly. But just one point here can I round it up result like if it’s 0.002 then <1 , if it’s 0.999 then 1 . A kind of rounded figures.
@lauma.cirule total days in each status are including only working days(mon-fri) or total 7 days of week.
What if I only want the working days in my report?
