Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Previous value with criteria

Hi, Everyone! I have a table of industrial measurements, that happens every minute. Therefore, i have a Column with name place, a column with Timestamp (date and time in minutes), and a column val...
Need Help
Tutorial Requests
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    Hi Anonymous ,

     

    I hope I understood your requirement. Typically I highly prefer using measures but in this case I tried it with a calcualted column.

     

    So the following formula identifies in the first place the last relevant row for Nome de Origiem (var_EarlierEntryOfNomeDeOrigem). Based on the time of the last relevant row it gives you back the related value (formula after result).

     

     

     

    Value before for name = 

var EarlierEntryOfNomeDeOrigem =
    CALCULATE(
        MAX(fminuto[Timestamp]),
        FILTER(
            ALL(fminuto),
            fminuto[Nome de Origem]=EARLIER(fminuto[Nome de Origem]) 
            && fminuto[Timestamp]<EARLIER(fminuto[Timestamp])
        )
    )


RETURN
CALCULATE(
    SELECTEDVALUE(fminuto[Value],BLANK()),
    FILTER(
        ALL(fminuto),
        fminuto[Nome de Origem]=EARLIER(fminuto[Nome de Origem]) 
        && fminuto[Timestamp] = EarlierEntryOfNomeDeOrigem
    )
)

     

     

    Please be aware that if there is no or  more than one value for the same time in one category then the formual gives back BLANK(). When there is one distinct previous value for the category (Nome de Origem) then it should work.

     

    Best regards

    Michael

    -----------------------------------------------------

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

    @ me in replies or I'll lose your thread.

     

Mikelytics's avatar
Mikelytics
Icon for Resident Rockstar rankResident Rockstar
3 years ago

Hi Anonymous ,

 

I hope I understood your requirement. Typically I highly prefer using measures but in this case I tried it with a calcualted column.

 

So the following formula identifies in the first place the last relevant row for Nome de Origiem (var_EarlierEntryOfNomeDeOrigem). Based on the time of the last relevant row it gives you back the related value (formula after result).

 

 

 

Value before for name = 

var EarlierEntryOfNomeDeOrigem =
    CALCULATE(
        MAX(fminuto[Timestamp]),
        FILTER(
            ALL(fminuto),
            fminuto[Nome de Origem]=EARLIER(fminuto[Nome de Origem]) 
            && fminuto[Timestamp]<EARLIER(fminuto[Timestamp])
        )
    )


RETURN
CALCULATE(
    SELECTEDVALUE(fminuto[Value],BLANK()),
    FILTER(
        ALL(fminuto),
        fminuto[Nome de Origem]=EARLIER(fminuto[Nome de Origem]) 
        && fminuto[Timestamp] = EarlierEntryOfNomeDeOrigem
    )
)

 

 

Please be aware that if there is no or  more than one value for the same time in one category then the formual gives back BLANK(). When there is one distinct previous value for the category (Nome de Origem) then it should work.

 

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

 

Mikelytics's avatar
Mikelytics
Icon for Resident Rockstar rankResident Rockstar
3 years ago

Hi Anonymous ,

 

I also managed to get the result when using a measure.

 

Measure Version Previous Row of Category = 

var EarlierEntryOfNomeDeOrigem =
    CALCULATE(
        MAX(fminuto[Timestamp]),
        FILTER(
            ALL(fminuto),
            fminuto[Nome de Origem]=SELECTEDVALUE(fminuto[Nome de Origem]) 
            && fminuto[Timestamp]<MAX(fminuto[Timestamp])
        )
    )


RETURN
CALCULATE(
    SELECTEDVALUE(fminuto[Value],"no value or multiple"),
    FILTER(
        ALL(fminuto),
        fminuto[Nome de Origem]=SELECTEDVALUE(fminuto[Nome de Origem]) 
        && fminuto[Timestamp] = EarlierEntryOfNomeDeOrigem
    )
)

 

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Mikelytics ! 

     

    Thank you for your answer and agility!

    Obviously, you code is correct, once it worked for you. Unfortunetly for me, it's retrieving "out of memory" error in both ways, calculated column (explicit error) and measure (loads forever). I've tried some tips found in this forum, like editing some settings and stuff, but no success. 

    I'll put your answer as the correct one, but if you know how to solve this, I'll be very grateful!

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Mikelytics I'm trying to use your measure, but now i have to quantify when the previous value is equal to the actual one, and I tried to use IF 1 or 0, like the code below. Although, It returned only 1's for me. Could you check it out, please?

    Congelamento =
    VAR EarlierEntryOfNomeDeOrigem =
        CALCULATE(
            MAX(fminuto[Timestamp]),
            FILTER(
                ALL(fminuto),
                fminuto[Nome da Origem]=SELECTEDVALUE(fminuto[Nome da Origem])
                && fminuto[Timestamp]<MAX(fminuto[Timestamp])
            )
        )

    VAR EarlierEntryOfVazao = CALCULATE(
        SELECTEDVALUE(fminuto[Vazão],"no value"),
        FILTER(
            ALL(fminuto),
            fminuto[Nome da Origem]=SELECTEDVALUE(fminuto[Nome da Origem])
            && fminuto[Timestamp] = EarlierEntryOfNomeDeOrigem
        )
    )

    VAR Congela = IF(EarlierEntryOfVazao=SELECTEDVALUE(fminuto[Vazão],"no value"), 1, 0)

    RETURN
    Congela