undefined

Question

Hi, I am trying to get the previous timestamp value in each column so I can calculate the change over the previous hour. I have attached a screen shot of the columns. Each row of data is pulled on an hourly basis. Any help you can provide is much appreciated. Thank you

alexisolson · Answer

This seems like a nice use case for OFFSET.
&nbsp;
Prev_tsCST = 
SELECTCOLUMNS (
    OFFSET (
        -1,
        TableName,
        ORDERBY ( TableName[tsCST], ASC )
    ),
    TableName[tsCST]
)
&nbsp;
The more traditional method would be to take a max over a filtered table:
&nbsp;
MAXX (
    FILTER ( TableName, TableName[tsCST] &lt; EARLIER ( TableName[tsCST] ) ),
    TableName[tsCST]
)
&nbsp;

pquinlan21 · Answer

I couldnt get the frist option to work because it said that Select was not a function.&nbsp; The screenshot above was be doing the second option and it was just producing the same TSCST value rather than the previous date/value.&nbsp;&nbsp;Ideally what i would like to create is a new column that shows the previous data row recorded. For example with column BHCC_WQI59914B_OUT would be:BHCC_WQI55914B_OUT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tsCST2859.609375&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/12/2024 10:00:00 AM2848.701660156&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/12/2024 9:00:00 AM2837.9265&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/12/2024&nbsp; 8:00:00 AM2828.04833&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/12/2024 7:00:00 AM........&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ..........&nbsp;

alexisolson · Answer

My bad. I wrote SELECT instead of SELECTCOLUMNS in the first and "&lt;=" instead of "&lt;" in the second. I've corrected this above.
&nbsp;
As to your second point, OFFSET returns a row, so you can select a different column rather than the timestamp column:
Prev_tsCST = 
SELECTCOLUMNS (
    OFFSET (
        -1,
        TableName,
        ORDERBY ( TableName[tsCST], ASC )
    ),
    "Prev_BHCC_WQ159914B_OUT", TableName[BHCC_WQ159914B_OUT]
)

pquinlan21 · Answer

Hi, this is the error I got back. Thank you for the help

alexisolson · Answer

Sorry, that image is too tiny to be readable. Can you share the text of the error message?

Forum Discussion

undefined

9 Replies

Related content

undefined

undefined

undefined

undefined

undefined