Forum Discussion
Calculate time between dates for each unique identifier
Hello v-kelly-msft,
Thank you for your answer. I need to plot the time as the x-axis on a line graph, and it doesnt seem possible with a measure. Is there something else i could do? Maybe alter this measure to be a column?
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
Would it be possible to also show me the power query version of this? I want to change the type to duration, then hopefully be able to plot it on a line graph as continuous. Currently i can only plot it as categorical as this is done in dax.
I have posted a question here https://community.powerbi.com/t5/Desktop/Plotting-Duration-as-continuous-line-graph/m-p/1243805#M549689
I would really appreciate if you an give me a hand.
Thank you,
Adham
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?