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 value. I'm trying to achieve the previous value (either with measure or calculated column), i.e, if the measurement refers with a same name place, it will retrieve the value immediately before it, for reasons of future comparison between the actual and previous value, in each row.

I've tried some resolutions found here, like this, but it did'nt work.

The core code I tried:

CALCULATE (

SUM(fminuto[Vazão]),

FILTER (

ALL ( fminuto ),

fminuto[Timestamp]

= CALCULATE (

MAX ( fminuto[Timestamp] ),

FILTER ( ALL ( fminuto ), fminuto[Timestamp] < SELECTEDVALUE( ( fminuto[Timestamp] ) )

)

))

Tried it e a lot of variations, but no success. Could Anyone help on this, please?
name column = "Nome de Origem"
DateTime with minutes column = "Timestamp"
Value column = "Vazão"

There is a sample of the table columns mentioned. Millions of rows.

Need Help

Tutorial Requests

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.

6 Replies

Mikelytics
Resident 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
  Resident 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
    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!
mangaus1111
Solution Sage
3 years ago
Hi Anonymous ,
is this your expected result?
Ashish_Mathur
Super User
3 years ago
Hi,

Try this calculated column formula

=lookupvalue(data[value],data[date],calculate(Max(Data[Date]),filter(Data,Data[Place]=earlier(Data[Place])&&data[date]<earlier(data[Date])))data[place],data[place])

Hope this helps.