Return immediately previous value

[mariana_carmof]

Hi everyone!

I need to check if my previous status is different from my current one. However, the initial comparison to proceed with my code does not return what I want.
In image formula, MAX returns the highest value of the status evaluated in the dates before my current line, however I just want the occurrence immediately before.

Also, because it's text:  inativo > ativo

 Expected result: 
Sem histórico

Ativo

Ativo

Ativo

Inativo

Sem histórico

Ativo

Inativo

Ativo

Ativo

Sem histórico

Inativo

Ativo

Inativo

Ativo

Can someone help me?

[mariana_carmof · Accepted solution]

Hey @speedramps!
Thanks for your answer!

I wanted to compare the "Status" column of the most recent date with the "Status" of the previous most recent date for a sku. 

I solved the problem as follows:

Alteracao_status_semanal =

VAR DATA_ANTERIOR = CALCULATE(MAX('Mkp combinado'[Data]),FILTER (ALLEXCEPT ( 'Mkp combinado','Mkp combinado'[Mkp - SKU]),'Mkp combinado'[Data] < EARLIER ( 'Mkp combinado'[Data] ) ))

VAR STATUS_ANTERIOR = CALCULATE(MAX('Mkp combinado'[Status Mkp]), FILTER(ALLEXCEPT('Mkp combinado','Mkp combinado'[Mkp - SKU]),'Mkp combinado'[Data] = DATA_ANTERIOR))

VAR ALTERACAO_STATUS = SWITCH(

TRUE(),

STATUS_ANTERIOR = "", "Sem histórico",

STATUS_ANTERIOR = 'Mkp combinado'[Status Mkp], "Inalterado",

STATUS_ANTERIOR = "Ativo" && 'Mkp combinado'[Status Mkp] = "Inativo", "Desativado",

"Ativado")

RETURN

ALTERACAO_STATUS

[speedramps]

Please can you explain more clearly what you want so we can help. Thanks.

1) Do you want to compare the most recent date with the previous most recent date for a sku?

2) Or do you want to compare the last row wtth previous row for each sku 

Option 1 answer ....

Current status =
VAR mylastdate = CALCUALTE( MAX(Planline[date], ALLEXCEPT(Planline[sku]) 
RETURN
CALCULATE(
SELECTVALUE( Planline[status],"More than one status per day"),

ALLEXCEPT(Planline[sku]), 
Planline[date] = mylastdate)

Previous status =
VAR mylastdate = CALCUALTE( MAX(Planline[date], ALLEXCEPT(Planline[sku]) 
VAR mypreviousdate =
CALCUALTE( MAX(Planline[date],
ALLEXCEPT(Planline[sku]),
Planline[date] < mylastdate)
RETURN
CALCULATE(
SELECTVALUE( Planline[status],"More than one status per day"),

ALLEXCEPT(Planline[sku]), 
Planline[date] = mypreviousdate)

Option 2

same as above except use an index instead of a date

 Please click thumbs up and accept as solution