Multiple snapshot times; convert Date/Time to Date and only keep the latest snapshot values

[iDataDrew]

I have tables that consist of projects with their associated costs.  The problem I'm running into is that sometimes multiple snapshots are taken on the same day, resulting in multiple rows for the same project on the same day.  See sample image below.  You can view sample data here. 

Here's the problem I'm running into.  I want to visualize the Cost field with a line chart.  If I keep the HistoryDate field as Date/Time, then there's a data-point for each Date/Time value (so multiple data-points for the same day).  The shape of the line is fine, but I want each data-point to represent a single day.  If I convert the field to a data type of Date then the values for each snapshot time within the same day get summed (e.g. 9,744 + 9,744 + 9,744 = 29,232 instead of just 9,744).  This results in large spikes on days where multiple snapshots have been taken.

I would prefer to do something in the Query Editor that makes it where it only keeps the latest snapshot time for each day per project and deletes the earlier snapshots.  This sounds like it would require a lot of M code though and I'm not advanced in this area.  Is this possible?  Or is there another solution using DAX?  I've already asked my manager if something could be done on the DB side of things, but it appears this won't be possible.  Thanks for your help.

[v-chuncz-msft · Accepted solution]

@iDataDrew,

You may follow the following steps.

1) Add a custom column [Date] and change type to Date

=[HistoryDate]

2) Sort [HistoryDate] in descending order

3) Hold the Ctrl key to select [Project Num] and [Date], then Remove Rows > Remove Duplicates

[iDataDrew]

@v-chuncz-msft do you know how I might write a measure that references the MIN() time (Date/Time) for each day?

[iDataDrew]

@v-chuncz-msft, thanks for the reply.  This works...partially.  It keeps the earlier snapshot time and removes the latest one, no matter how I sort it.  This may suffice though.  I think most of the rows are the same, so it doesn't matter which one gets removed.  If there are differences in the rows, I don't think they're major, and the latest removed duplicate would just show up in the next snapshot time for the next day.  So I don't think anything is really being lost.

[v-chuncz-msft]

@iDataDrew,

See solutions in links below that use Power Query and DAX. For any new question, please post a new message.

• https://community.powerbi.com/t5/Desktop/Remove-Rows-in-Dataset-based-on-most-recent-ID-and-email-ad...
• https://community.powerbi.com/t5/Desktop/how-can-I-show-top-3-data-with-a-combined-categories/td-p/1...