Time in status for previous period of time

Questions & Answers
,
1

HI!
I want to get time in status for each issue in my project.
I use this calculated member for calculating time

NonZero(Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Issues history] >=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 history] > 0,
– [Time].CurrentMember, 0)
DateDiffDays(
[Measures].[Issue status updated date],
Now()), 0)
))

But i want to get this calculated measure for previous period.
I use Time dimention for pages and Issues as rows.
When I selected previous period of time, it calculated wrong values.
I understand that error in this part:
DateDiffDays(
[Measures].[Issue status updated date],
Now()), 0)

row with Now() should be replaced to measure with Time dimension.
When I use [Time].CurrentMember it not calculate last transition status for selected period.

Example:
Issue ATLAS-1 was created 7 May 2019 at 3:40 pm;
At 3:50 pm status was changed from To Do to In Progress.
At 4 pm status was changed from In Progress to QA and 8 May 2019 at 9:20 am was closed.
When i choose 7 May i want to get next values: ATLAS-1 was 10 minutes in To Do status, 10 minutes in status In progress and 8 hours in status in progress.
What should I do to get this values from my measure?

Bohdan

2 
NonZero(
  Cache(
    -- days in transition status when issue was in this status previously
    [Measures].[Days in transition status] +
    -- days since last transition to this status
    IIF([Measures].[Issues history] > 0,
      DateDiffDays(
        ( [Measures].[Transition to status last date],
          [Time].CurrentHierarchy.DefaultMember),
        [Time].CurrentHierarchyMember.NextStartDate
        ), 0
    )
  )
) * 24 * 60

This calculated measure partially solve my problem, but when I chose Nonempty for issue rows all my issues hide.

3

Hi,

The last code you found should not hide values when “Nonempty” is enabled.
Perhaps you can export and share the report definition with us.

Martins / eazyBI team

1 Like
4

Hi, Martins!
Can you help me with measure Days in transition status?
I still have problem, described on fiirst memssage of this topic.
How can i get time in status on specific date?

for example
issue was created 5 october and was in status In progress 5 days(from 5 october to 10 october)
when i use time in transition status, i get value 5 days when choose 10 october.
I want get time value for 6 october(issue was in progress 24 hour)
how cat i deal with it?

5

Hi

If nothing happened in 6th Oct, this date would not automatically be created in “Time” dimension and the results would not show the difference between last transition and that date (6th Oct).
Try adding missing date members (6-9 oct) manually to “Time” dimension.
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-AddmemberstoTimedimension
Then your calculation should take them into account when calculating results.

Martins / eazyBI team