How do I create a custom measure to show amount of time in CURRENT status? I know how to calculate time between two dates in the epic, but I want to calculate the elapsed time between [Measures].[Issue Project Status Updated Date] and today. I feel like there has to be an obvious way to do this that I am overlooking.
Hello @hjd
Try this formula (measure name is “days in status”):
IIf(
IsEmpty([Measures].[Transition from status last date]),
DateDiffWorkdays(
[Measures].[Transition to status first date],
Now()
)
,
DateDiffWorkdays(
[Measures].[Transition to status first date],
[Measures].[Transition from status last date]
)
)
It calculates the number of workdays an issue stay(-ed) in a status. The logical expression in my IIF statement (IsEmpty(..) ) refers to the current issue status (there is no “transition from” date yet!).
My chart looks like this (on “rows” there’s only “Issue” dimension):
Kind regards,
Vasile S.
Hi @hjd,
@VasileS already provided one of the options related to the issue status changes.
If you want to find the time from a specific timestamp property until now, you might use the DateDiffDays function with the particular property and “now” as relative time or Now() as the time function.
The expression might look as follows.
DateDiffDays(
[Measures].[Issue Project Status Updated Date],
Now()
)
Regards,
Oskars / support@eazyBI.com
When using this formula, I still see some unexpected behaviour.
- for tickets which have been resolved, it will return the amount of days needed to resolve the tickets, so from Creation date till date of resolution
- for tickets which haven’t been resolved, it will show way more days as the actual date it was in this status. I guess some corrections are needed.
I made following corrections, which seem to give better results:
IsEmpty([Measures].[Transition from status last date]),
DateDiffDays(
[Measures].[Transition to status first date],
Now()
)
,
DateDiffDays(
[Measures].[Transition from status last date],
Now()
)
)