Forum Discussion
Calculate time between dates for each unique identifier
Hello All, I have been stuck with this issue for a while and i would appreciate some help. I have got the following table. ID Date 1 16/07/2020 14:11:12 1 17/07/2020 15:12:11 1...
Hi Adham ,
Yes,change the measure into calculated column :
_Time =
var _mindate=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])))
var _daydiff=DATEDIFF(_mindate,'Table'[Date],HOUR)
Return
_daydiff&":"&FORMAT('Table'[Date]-_mindate,"nn:ss")
For modified .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hello v-kelly-msft,
I actually just realised something. If you like at the table below you will see something odd when i apply the formula to have a column with the times.
| Date | Calculated Time | Should be |
| 21/07/2020 14:44:19 | 0:00:00 | |
| 21/07/2020 15:00:00 | 1:15:41 | 0:15:41 |
| 21/07/2020 15:44:19 | 1:00:00 | |
| 21/07/2020 15:55:19 | 1:11:11 |
When the hour in the datetime stamp hits 15 it assumes an hour has passed which is incorrect. It will assume an hour has passed all the way till 15:44:18 where the correct time is calculated and adjusted at 15:44:19. However, when the time hits 16:00:00 the calculated time difference will be 2:15:41 and so on. This trend is observed throughout the entire column. How can i fix this issue?