How to show only the latest data for each timestamp

[pbidemouser2]

 Hi all,

I am working with a live data connection which gets data in a hourly basis. I am facing difficulties in using DAX. I have a dataset similar to the one below.

Timestamp      Value

15/06/2020 1:00    10

15/06/2020 1:00     12

15/06/2020 1:00    15

15/06/2020  2:00    11

15/06/2020  2:00    13

I want to show only the last value for each timestamp. For ex., i want the output to show as below,

Timestamp    Value

15/06/2020 1:00   15

15/06/2020 2:00    13

Could someone pls suggest a way to do this?. Since this is a live connection i couldnt use the query editor. Is there any measure for this. After this, I want to plot the timestamp vs Value in the form of a line plot.

Thanks,

@amitchandak @parry2k @Greg_Deckler 

[amitchandak]

@pbidemouser2 , Try if this can help

LASTNONBLANKVALUE(Table[timestamp],sum(table[Value]))

[pbidemouser2]

Timestamp is not a measure here @amitchandak 

[v-lionel-msft]

Hi @pbidemouser2 ,

Please refer to the below formula.

Measure 2 = 
LASTNONBLANKVALUE(
// the name of my table is 'Sheet4'
    Sheet4[Timestamp],
    MAX( Sheet4[Value] )
)

Best regards,
Lionel Chen

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

[pbidemouser2]

HI @v-lionel-msft ,

I tried the measure u told. The Sheet4[Timestamp] part doesnt work. 

Since the Timestamp column isnt a measure here, i couldnt use it in this. Could you pls suggest me what to do?

Thanks

[v-lionel-msft · Accepted solution]

Hi @pbidemouser2 ,

This is very strange.

Are you saying that you can‘t use the [Timestamp] column in the LASTNONBLANKVALUE() function?

What error did you encounter?

Please send a screenshot of your error message.

Or try this measure formula.

Measure 3 = 
CALCULATE(
    MAX(Sheet4[Value]),
    ALLEXCEPT(Sheet4, Sheet4[Timestamp] )
)

Best regards,
Lionel Chen

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

[pbidemouser2]

Hi @v-lionel-msft ,

This measure shows the max value of each timestamp, not the latest one. Is there any other way to find the latest one?

Thanks

[pbidemouser2]

Thanks @v-lionel-msft , this measure worked