Time in State

[atarasov]

Hi, 

I have a column "Active" that can have two values TRUE or FALSE. I want to log time in state (days: hours: minutes) for TRUE or FALSE values in another column "TimeInState" and reset the clock when state changes. Is it possible to achive with PowerBI and if yes, what is the solution?

Thank you.

[V-lianl-msft]

Hi @atarasov ,

Based on your description, what you want to achieve is to calculate the duration of a state. When it updates the state, it will reset the time to zero.

Unfortunately, I'm afraid it's hard to achieve according to my attempt.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @atarasov 

If you have a Time column near the State column you could create a measure

TimeInState =
var _lastPrevState = calculate(MAX(Table[Time]), FILTER(ALL(Table), Table[State] <> SELECTEDVALUE(Table[State]) && Table[Time] < SELECTEDVALUE(Table[Time]) ) )
var _firstActiveState = calculate(MIN(Table[Time]), FILTER(ALL(Table), Table[State] = SELECTEDVALUE(Table[State]) && Table[Time] > _lastPrevState  ) )

RETURN
DATEDIFF(_firstActiveState , SELECTEDVALUE(Table[Time]), MINUTE)

[atarasov]

Thank you, what is in Time column? 

[az38]

@atarasov 

what column do you want to store log datetime in?

[atarasov]

Thanks, I did what you have suggested, but both columns have no values in it.

What am I missing? 

Thanks.