Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

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.


116/07/2020 14:11:12
117/07/2020 15:12:11
116/07/2020 15:32:44
116/07/2020 17:11:24
219/07/2020 06:11:12
219/07/2020 12:12:11
220/07/2020 14:32:44
220/07/2020 02:11:24


I would like to calculate the time by using the minimum value for each ID and subtracting it for rows that hold the same ID. However, i want the format to be in hh:mm:ss where if the difference is more than 24 hours it would be 37:44:22 for example.


Here is how i would like the results should look like.


116/07/2020 14:11:12  00:00:00

17/07/2020 18:32:23  

116/07/2020 18:32:44  04:21:32
116/07/2020 17:11:24  00:00:12
219/07/2020 06:11:12  00:00:00
219/07/2020 12:12:12  06:01:00
220/07/2020 14:32:44  26:20:32


I would prefer to do in the power query, as i tried doing something similiar in DAX, and after uploading it to power bi service the time axis isnt as expected as it assumes 00:00:00 is 12:00 AM. If done in power query the column type could be changed however.

Community Support
Community Support

Hi  @Adham ,


Create a measure as below:

Time = 
var _mindate=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])))
var _daydiff=DATEDIFF(_mindate,MAX('Table'[Date]),HOUR)

And you will see:

Annotation 2020-07-22 170000.png

For details,pls see attachment.


Best Regards,
Did I answer your question? Mark my post as a solution!

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)

For modified .pbix file,pls see attached.


Best Regards,
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?

I have posted a question here


I would really appreciate if you an give me a hand.


Thank you,



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.

Super User
Super User

@Adham , Try like

time = Table[Date] -maxx(filter(Table,Table[Date] <earlier(Table[Date]) && Table[ID] =earlier(Table[ID])),Table[Date])

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Super User
Super User

Power Query has a duration data type, but this is converted to decimal number when loaded into the report:

See if this link helps you calculate the difference between two rows using Power Query:

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.

Helpful resources


2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.