Forum Discussion

mariana_carmof's avatar
mariana_carmof
Frequent Visitor
4 years ago
Solved

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 re...
  • mariana_carmof's avatar
    mariana_carmof
    4 years ago

    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's avatar
speedramps
Icon for Super User rankSuper User
4 years ago

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