Plot latest data only

[gdssiqueira]

Hello,

I'm new to PowerBI and DAX so I apologize if this is overly simple.

I have a table with values and dates associated. Ex:

Date  Value

1/1       15

2/1       20

3/1       12

4/1       15
I want to make a line chart to show how this value evolves over time. This is pretty straightforward.

Now suppose I have a third column containing a timestamp for when that value was obtained. I might have more than one value per date, and I want to plot only one per date, choosing the one with the latest timestamp. Ex:
Date Value Timestamp

1/1    15      5/1

2/1    20      4/1

2/1    19      5/1

3/1    12      3/1

3/1    15      4/1

4/1    15      5/1

In this case, I want to plot the data that was not crossed out. Is there a simple way to achieve this?

Thanks!

[gdssiqueira]

@ckodadek @Vvelarde

Thank you for your suggestions. I will try them and then get back with the results. The data filtering has to be dynamic through PowerBI, since the database is automatically updated.

@ankitpatira

Wow, thank you for the guide with screenshots. That's super helpful! One more question: in my case, date and value are not on the same table, I just presented you with a simplification of the problem. Perhaps I oversimplified, let me try to be more accurate.

Table1 has:
ID Date Time  and other info not relevant to the problem

Table2 has:
ID Type Value Time and other info not relevant to the problem

ID and Time are the same for both tables(PK for the first, FK for the second), so I'd like to plot, say, Values over Dates - filtering by type of value using a Slicer. One ID is only linked to one Date, so that's not to worry (although there are multiple IDs that happened on the same date) and each row in the first table might be related to one or more rows in the second table (different values for different value types). Would that significantly change the solution?

Thanks once again!

[ankitpatira · Accepted solution]

@gdssiqueira Here's the alternative to DAX, 

In power bi desktop go to query editor, right click your table -> Duplicate.

For duplicated table click Group By as below.

This will give you date with latest time. Then click Merge Queries and merge it as below.

Expand new column to include Value column as below and Close & Apply. You will have two tables the second one will have data you want and every time you refresh both tables will be updated.

[gdssiqueira]

@ankitpatira

Merging queries doesn't allow me to use any newly created columns, does it?

[Vvelarde]

Create a measure:

MaxTimeStamp = CALCULATE(max('Date-Stamp'[Value]);FILTER('Date-Stamp';MAX('Date-Stamp'[TimeStamp])))

And use this for the chart

[ckodadek]

I believe you could de-concatenate the timestamp/dates. Then use =MAX({timestampCells}:{timeStampCells}) to get the max value of the time stamp, by comparing the 5 versus the 4, or versus the other digits...
Also, you'd probably want some sort of column to store the max time stamp values of each date along with the corresponding values.
Seems like you could do this in excel then import it?