Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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])

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.