Calculate the sum of a metric based on most recent date, with multiple records for a single date

[jklein]

I am building a time tracking report for my company so that leadership can view each employee's weekly time entered. The way our system works is if an employee enters time, "8 hours" and then updates that same day's entry to say "10 hours" it enters a new record. So the sum for that day is "18 hours". I want only to include the later entry. I have a field UPDATED_AT which I am trying to use in a filter to accomplish this. 

This is what I have:

CALCULATE([Time in Hours], TIMEENTRIES[UPDATED_AT]=MAX(TIMEENTRIES[UPDATED_AT]))

But it is only returning the sum based on the single max date, so if the employee added 8 hours at the end of the week, it is showing 8 hours as it is the latest updated_date. How can I get write this so it knows to ignore the older entry for a single day?

[v-rongtiep-msft]

Hi @jklein ,

I have some doubt. Please help. 

If I enter 8 on Monday,  then it will update another to 10, so the all value is 18. If I enter 8 on Sunday, then it still is 8, and the total value is 8?

Is that what I understand?

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[jklein]

@v-rongtiep-msft No. Say you enter 8 hours for the day today. But then you end up working 10 hours. So you change your entry in the system to 10 hours. There are now 2 records: the one you entered as 8 and the one you entered as 10. So in powerbi it is taking all the records for that day and summing them up, but I need it to ignore the older record (the 8 hours) and keep the new record (10 hours). Have a look at the screenshot (the bottom chart), it will explain what I am saying.

[v-rongtiep-msft · Accepted solution]

Hi @jklein ,

Is there both the column I entered and the updated column in the table? If so, you can create another column based on them.

Create a column.

column=if('table'[hours]>'table'[updated],'table'[updated],'table'[hours])

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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