cancel
Showing results for
Did you mean:
gudns Frequent Visitor

## How to get how much time a row was in the same state

So, my problem is:

I have a bunch of equal IDs, and I want to check when a value is changed in another column. When changed, I need to return how much time the value was the same before changing and say what it was and what it became:

Each ID appears for the same amount, and each one have a status column, here's an example with one of the IDs:

 id status date_check 1135381099869705 x a 1135381099869705 x b 1135381099869705 x c 1135381099869705 x d 1135381099869705 y e 1135381099869705 y f 1135381099869705 y g 1135381099869705 z h 1135381099869705 z i 1135381099869705 z j 1135381099869705 z k 1135381099869705 z l 1135381099869705 z m

Basically, I need to check when X became Y, then return the difference of dates e and a (I already have these dates). The same for when Y becomes Z.

1 ACCEPTED SOLUTION

Accepted Solutions Super User

## Re: How to get how much time a row was in the same state

```NewColumn =
VAR CurrentStatus_ = Table1[status]
VAR FollowingDate_ =
CALCULATE (
MIN ( Table1[date_check] );
Table1[date_check] > EARLIER ( Table1[date_check] );
ALLEXCEPT ( Table1; Table1[id] )
)
VAR FollowingStatus_ =
CALCULATE (
VALUES ( Table1[status] );
Table1[date_check] = FollowingDate_;
ALLEXCEPT ( Table1; Table1[id] )
)
VAR Status_Transition_ = CurrentStatus_ <> FollowingStatus_
RETURN
IF (
Status_Transition_;
VAR FollowingStatus_StartDate_ =
CALCULATE (
MIN ( Table1[date_check] );
Table1[status] = FollowingStatus_;
ALLEXCEPT ( Table1; Table1[id] )
)
VAR CurrentStatus_StartDate_ =
CALCULATE (
MIN ( Table1[date_check] );
ALLEXCEPT ( Table1; Table1[id]; Table1[status] )
)
RETURN
IF (
NOT ISBLANK ( FollowingStatus_StartDate_ );
FollowingStatus_StartDate_ - CurrentStatus_StartDate_
)
)```
12 REPLIES 12 Super User

## Re: How to get how much time a row was in the same state

Hi @gudns,

Try creating a new calculated column:

```DateDiffAtTransition =
VAR CurrentStatus_ = Table1[status]
VAR PreviousDate_ =
CALCULATE (
MAX ( Table1[date_check] );
Table1[date_check]
< EARLIER ( Table1[date_check] );
ALL ( Table1[status] )
)
VAR PreviousStatus_ =
CALCULATE (
VALUES ( Table1[status] );
Table1[date_check] = PreviousDate_;
ALL ( Table1[status] )
)
VAR Status_Transition_ = CurrentStatus_ <> PreviousStatus_
RETURN
IF (
Status_Transition_;
VAR PreviousStatus_StartDate_ =
CALCULATE (
MIN ( Table1[date_check] );
Table1[status] = PreviousStatus_;
ALL ( Table1[date_check] )
)
VAR CurrentStatus_StartDate_ = Table1[date_check]
RETURN
CurrentStatus_StartDate_ - PreviousStatus_StartDate_
)
```

gudns Frequent Visitor

## Re: How to get how much time a row was in the same state

Hey @AlB, thanks for your response!

I tried but I'm getting the "circular dependancy" error. Any idea why? Super User

## Re: How to get how much time a row was in the same state

I'd need to see the code you are using in other calculated columns. I'm guessing you use some form of CALCULATE that triggers context transition and that creates the circular dependency.

If date_check were the actual dates, without calculations, it should work.

gudns Frequent Visitor

## Re: How to get how much time a row was in the same state

The date column contains the actual date values, it's not a calculated column. I can't find the problem.

gudns Frequent Visitor

## Re: How to get how much time a row was in the same state

I tried with only one block of IDs, just like the example I gave you and it works!

However, I have several "blocks" of IDs in the same table, and I have to check the status change of each block of ID.

Example:

 ID STATUS DATE CHECK 1135428471827319 x a 1135428471827319 x b 1135428471827319 x c 1135428471827319 y d 1135428471827319 y e 1135428471827319 y f 1135428471827319 z g 1135428471827319 z h 1135428471827319 z i 1135381099869705 x a 1135381099869705 x b 1135381099869705 y c 1135381099869705 y d 1135381099869705 y e 1135381099869705 z f 1135381099869705 z g 1135381099869705 z h

First I have to check considering the 1135428471827319 id and then 1135381099869705 and so on.

Highlighted Super User

## Re: How to get how much time a row was in the same state

Try this:

```DateDiffAtTransition =
VAR CurrentStatus_ = Table1[status]
VAR PreviousDate_ =
CALCULATE (
MAX ( Table1[date_check] );
Table1[date_check] < EARLIER ( Table1[date_check] );
ALLEXCEPT ( Table1; Table1[ID] )
)
VAR PreviousStatus_ =
CALCULATE (
VALUES ( Table1[status] );
Table1[date_check] = PreviousDate_;
ALLEXCEPT ( Table1; Table1[ID] )
)
VAR Status_Transition_ = CurrentStatus_ <> PreviousStatus_
RETURN
IF (
Status_Transition_;
VAR PreviousStatus_StartDate_ =
CALCULATE (
MIN ( Table1[date_check] );
Table1[status] = PreviousStatus_;
ALLEXCEPT ( Table1; Table1[ID] )
)
VAR CurrentStatus_StartDate_ = Table1[date_check]
RETURN
IF (                NOT ISBLANK ( PreviousStatus_StartDate_ );                CurrentStatus_StartDate_ - PreviousStatus_StartDate_            )    )
```

gudns Frequent Visitor

## Re: How to get how much time a row was in the same state

That worked wonderfully. The last thing I'm trying to do is return the number on the row of the previous stat, not the new one. Super User

## Re: How to get how much time a row was in the same state

I think you should be able to get to that with minor changes to the last version. Give it a go, it will be a good exercise. If it doesn't work let me know and we'll have a look later. If the posts are of help please consider kudoing accordingly.

Cheers

gudns Frequent Visitor

## Re: How to get how much time a row was in the same state

I really tried, but I just can't do it. I feel like I'm so close, but I can't figure it out.

Announcements #### New Topics Started Badges Coming  