Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mariana_carmof
Frequent Visitor

Return immediately previous value

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

 

captura.PNG

 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?

1 ACCEPTED SOLUTION
mariana_carmof
Frequent Visitor

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



View solution in original post

2 REPLIES 2
mariana_carmof
Frequent Visitor

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.