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

Frequent Visitor

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

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?

Frequent Visitor

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

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

Announcements

#### 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

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

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)