cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Adham
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.

 

IDDate
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.

 

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

17/07/2020 18:32:23  

28:21:11
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.

8 REPLIES 8
v-kelly-msft
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)
Return
_daydiff&":"&FORMAT(MAX('Table'[Date])-_mindate,"nn:ss")

And you will see:

Annotation 2020-07-22 170000.png

For details,pls see attachment.

 

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

I have posted a question here https://community.powerbi.com/t5/Desktop/Plotting-Duration-as-continuous-line-graph/m-p/1243805#M549...

 

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

 

Thank you,

 

Adham

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.

amitchandak
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
AllisonKennedy
Super User
Super User

Power Query has a duration data type, but this is converted to decimal number when loaded into the report: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types

See if this link helps you calculate the difference between two rows using Power Query: https://www.myonlinetraininghub.com/referencing-next-row-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.
www.excelwithallison.com

Helpful resources

Announcements
Carousel_PBI_Wave1

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.