topic Re: Return immediately previous value in DAX Commands and Tips

Return immediately previous value

mariana_carmof — Mon, 09 May 2022 20:27:10 GMT

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

Inativo

Sem histórico

Ativo

Inativo

Ativo

Sem histórico

Inativo

Ativo

Inativo

Ativo

Can someone help me?

Re: Return immediately previous value

speedramps — Mon, 09 May 2022 20:46:27 GMT

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

Re: Return immediately previous value

mariana_carmof — Wed, 25 May 2022 18:54:35 GMT

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