undefined

[Pquinlan21]

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 

[Anonymous]

Hi, @Pquinlan21 

May I ask if you have solved this problem? You can try AlexisOlson's method to solve your problem, if it doesn't work, you can share the pbix file without sensitive data to help you solve the problem.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Pquinlan21]

 

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

[Pquinlan21]

@AlexisOlson 

[AlexisOlson]

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

[Pquinlan21]

OFFSET' Relation parameter may have duplicate rows. This is not allowed. 

@AlexisOlson 

[AlexisOlson]

Ah, I have seen this before.

Try adding MATCHBY as explained in great detail here:
https://pbidax.wordpress.com/2023/05/25/introducing-matchby-for-dax-window-functions/

Prev_tsCST = 
SELECTCOLUMNS (
    OFFSET (
        -1,
        TableName,
        ORDERBY ( TableName[tsCST], ASC ),
        MATCHBY ( TableName[tsCST] )
    ),
    "Prev_BHCC_WQ159914B_OUT", TableName[BHCC_WQ159914B_OUT]
)

If that column does not have unique values, then you'll need to add additional columns inside MATCHBY so their combination is something unique.

[Pquinlan21]

I couldnt get the frist option to work because it said that Select was not a function.  The screenshot above was be doing the second option and it was just producing the same TSCST value rather than the previous date/value. 

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            tsCST

2859.609375                              1/12/2024 10:00:00 AM

2848.701660156                         1/12/2024 9:00:00 AM

2837.9265                                  1/12/2024  8:00:00 AM

2828.04833                                 1/12/2024 7:00:00 AM

........                                              ..........

[AlexisOlson]

My bad. I wrote SELECT instead of SELECTCOLUMNS in the first and "<=" instead of "<" in the second. I've corrected this above.

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]
)

[AlexisOlson]

This seems like a nice use case for OFFSET.

Prev_tsCST = 
SELECTCOLUMNS (
    OFFSET (
        -1,
        TableName,
        ORDERBY ( TableName[tsCST], ASC )
    ),
    TableName[tsCST]
)

The more traditional method would be to take a max over a filtered table:

MAXX (
    FILTER ( TableName, TableName[tsCST] < EARLIER ( TableName[tsCST] ) ),
    TableName[tsCST]
)