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 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'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



2 Replies

  • speedramps's avatar
    speedramps
    Super 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

  • mariana_carmof's avatar
    mariana_carmof
    Frequent Visitor
    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