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

gudns Frequent Visitor
Frequent Visitor

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

@AlB 

 

Again, thank you so much for the atention you've been giving me. I've been learning so much trying to solve this problem with you. Your last code works, but the CurrentStatus_Start_Date_ is the MIN of the datas. Therefore, the only time the information is accurate, is on the first status change. When the second one occurs, the data should be the MIN + how many days was spent after that.

 

I'm trying to make your two formulas work together to solve this. Any insights?

gudns Frequent Visitor
Frequent Visitor

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

@AlB 

 

I actually got it. I used both of your formulas as the source of a third column. That column have everything I need: the days in the right places and the days of the current status. I named your columns "DatePreviousStatus" and "DateNewStatus". The code is probably a lot more convoluted than it should be, but since I'm a beginner, I'm pretty happy about it. Thank you again for your help.

 

TempostVaga =
VAR DateNothingChanged =
    CALCULATE (
        MIN ( consultaPastas[Date created] );
        ISBLANK ( consultaPastas[DatePreviousStatus] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR Real_Number_Of_Days_After_Transition_ =
    CALCULATE (
        MIN ( consultaPastas[Date created] );
        NOT ISBLANK ( consultaPastas[DateNewStatus] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR Real_Number_Of_Days_Before_Transition =
    CALCULATE (
        MAX ( consultaPastas[Date created] );
        NOT ISBLANK ( consultaPastas[DateNewStatus] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR Check_First_Change =
    CALCULATE (
        MIN ( consultaPastas[Date created] );
        NOT ISBLANK ( consultaPastas[DatePreviousStatus] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR Date_Of_First_Query =
    CALCULATE (
        MIN ( consultaPastas[Date created] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR Date_Aux =
    CALCULATE (
        MAX ( consultaPastas[Date created] ) + 1;
        NOT ISBLANK ( consultaPastas[DatePreviousStatus] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR DateOfLastQuery =
    MAX ( consultaPastas[Date created] )
RETURN
    IF (
        CALCULATE (
            MAX ( consultaPastas[DatePreviousStatus] ) = 0;
            ALLEXCEPT ( consultaPastas; consultaPastas[id] )
        );
        IF (
            consultaPastas[Date created] = DateOfLastQuery;
            DateOfLastQuery - DateNothingChanged
        );
        IF (
            NOT ISBLANK ( consultaPastas[DatePreviousStatus] );
            IF (
                consultaPastas[Date created] = Check_First_Change;
                Real_Number_Of_Days_After_Transition_ - Date_Of_First_Query;
                Real_Number_Of_Days_Before_Transition - Real_Number_Of_Days_After_Transition_
            );
            IF (
                consultaPastas[Date created] = DateOfLastQuery;
                DateOfLastQuery - Date_Aux
            )
        )
    )

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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)