Custom column evaluating condition of previous row

[Anonymous]

I would like to get a custom column like the one presented below (Cond.):

It needs to evaluate the values of two different columns but just in previous row. In this case conditions are:

If Hum. >= 80 and Temp. < 2 (red squares) --> next row in Cond. column = 1 (green squares)

I have tried code below, but without success.

IF ( CALCULATE (COUNTROWS ( Table1 ), FILTER ( Table1, EARLIER ( Hum. ) > = 80 && EARLIER ( Temp. ) < 2 ) ), 0 , 1 )

I am not sure about what function to use in order to evaluate the row before. Is that possible?

At the same time, I have tried next thread approach to get value from previous row but still does not work.

https://community.powerbi.com/t5/Desktop/DAX-getting-previous-value/td-p/489921

[Greg_Deckler]

Yeah @Anonymous the issue is, "what is next?" I realize that you can look at the table and see the next row down. Unfortunately, DAX does not work like that. There is no guarantee of order. As @amitchandak said, you have to have something that identifies it as "next". Often this is a date/timestamp or an Index column. You have to have some way to filter down to the row you want.

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586

[Anonymous]

Thak you both for your answers.

Let's say that I have a way of identify what is "next" based on datetime column (just as @Greg_Deckler said).

Would it ibe possible in this new scenario identify next row by means of EARLIER function?

[amitchandak]

@Anonymous , try 1st or second option should work

Last count = countx(filter(table,table[datetime]<earlier(table[datetime]) && table[hum] = 80 && table[temp]=1),table[datetime])
OR
Last count = countx(filter(table,table[datetime]<earlier(table[datetime]) && earlier(table[hum]) = 80 && earlier(table[temp])=1),table[datetime])

[Anonymous]

Thank you for your answer @amitchandak.

I have managed to evaluate your two options (Last_Count_Option1 and Last_Count_Option2). Next pic shows the results:

As you can see, both options cannot replicate values on column "Cond." which represent the desired outcome.

https://www.dropbox.com/s/0zqbl52jhwfanr7/EARLIER_Test.xlsx?dl=0 

[Greg_Deckler]

@Anonymous - I believe you want this formula:

Column = 
    VAR __Previous = MAXX(FILTER('Tabla4',[DateTime]<EARLIER(Tabla4[DateTime])),[DateTime])
    VAR __PreviousTemp = MAXX(FILTER('Tabla4',[DateTime]=__Previous),[Temp.])
RETURN
    IF(__PreviousTemp = 1,1,0)

PBIX is attached.

[Anonymous]

@Greg_Deckler Thank you for your answer.

That formula recreates exactly what I want to get! However, I am trying to understand it but I really do not know how conditions:

Hum. >= 80

Temp. < 2

 are included on it.

In fact, if I update the database with three more rows, It seems no longer works.

Could you, please, make some comments on this formula?

[Greg_Deckler · Accepted solution]

Sure,

Column = 
    VAR __Previous = MAXX(FILTER('Tabla4',[DateTime]<EARLIER(Tabla4[DateTime])),[DateTime])
    VAR __PreviousTemp = MAXX(FILTER('Tabla4',[DateTime]=__Previous),[Temp.])
RETURN
    IF(__PreviousTemp = 1,1,0)

The first line filters the table for all values in the [DateTime] column that are less than the current row's value for the [DateTime] column. Then it uses MAXX to grab the latest date from that filtered set. So, we now have the DateTime that immediately preceeds our current row, assuming that is how things really are in the data set, that things are recorded sequentially.

So, we can now use the value for __Previous that we calculated to find the value of the [Temp.] column for that row, __PreviousTemp

We then check if the __PreviousTemp is 1 and if so, return 1, otherwise 0.

[Anonymous]

@Greg_Deckler Thank you very much! Now, I understand how this structure works and I have been able to modify your answer to adapt it to my needs.

At the end, the solution to the question of this thread is next:

Column = 
    VAR __Previous = MAXX(FILTER('Table2';[DateTime]<EARLIER(Table2[DateTime]));[DateTime])
    VAR __PreviousTemp = MAXX(FILTER('Table2';[DateTime]=__Previous);[Temp.])
    VAR __PreviousHum = MAXX(FILTER('Table2';[DateTime]=__Previous);[Hum.])
RETURN
    IF(__PreviousHum >= 80  && __PreviousTemp <= 2;1;0)

[Greg_Deckler]

Awesome! Glad to help!

[amitchandak]

Is there a way to identify the row, datetime, id etc ??

[Anonymous]

@amitchandak The row is the the next at which  Hum. >= 80 and Temp. < 2. That's why I tried to use "EARLIER" function.

[amitchandak]

Earlier do need to create some kind of unique reference to get the previous row.

I think @Greg_Deckler is an expert on this. Greg, please have a look.