cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gudns Frequent Visitor
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:

 

idstatusdate_check
1135381099869705xa
1135381099869705xb
1135381099869705xc
1135381099869705xd
1135381099869705ye
1135381099869705yf
1135381099869705yg
1135381099869705zh
1135381099869705zi
1135381099869705zj
1135381099869705zk
1135381099869705zl
1135381099869705zm

 

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
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_
            )
    )

Code formatted with   www.daxformatter.com

View solution in original post

12 REPLIES 12
Super User
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_
    )

Code formatted with   www.daxformatter.com

 

 

gudns Frequent Visitor
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
Super User

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

@gudns 

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
Frequent Visitor

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

@AlB 

 

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

gudns Frequent Visitor
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: 

 

IDSTATUSDATE CHECK
1135428471827319xa
1135428471827319xb
1135428471827319xc
1135428471827319yd
1135428471827319ye
1135428471827319yf
1135428471827319zg
1135428471827319zh
1135428471827319zi
1135381099869705xa
1135381099869705xb
1135381099869705yc
1135381099869705yd
1135381099869705ye
1135381099869705zf
1135381099869705zg
1135381099869705zh

 

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

Highlighted
Super User
Super User

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

@gudns 

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
Frequent Visitor

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

@AlB 

 

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
Super User

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

@gudns 

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
Frequent Visitor

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

@AlB 

 

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)