Forum Discussion

atarasov's avatar
atarasov
New Member
6 years ago

Time in State

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.

Need Help

5 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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)

     

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    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.