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
Olifer
Frequent Visitor

Calculating time difference does not deliver the expected result

Dear community,

 

I need to calculate the time difference between a column named "time_val" (formatted as date/time) as provided in the image below and "1/1/1970 0:00:00". The result should be provided in hours. 

 Input DataInput Data

 

So I created a new column and tried to calculate the time difference with the following expression: 

duration = DATEDIFF(DateValue("1/1/1970"),SomeTable[time_val].[Date],HOUR)

 

Unfortunately, this only provides the duration in multiples of 24, so it basically seems to calculate the days and multiply the result with 24. What I would need, however, is the exact number of hours.

 

To give an example: For an entry "01.01.1970 14:20:00" I would expect a response of 14 (hours). What I get, however, is 0.

 

I'd highly appreciate any help.

 

Thanks and best regards

Oliver

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@Olifer 

Don't use the .date extension on the field.  That invokes the hidden auto time intelligence date field.  Try this.

duration = DATEDIFF(DateValue("1/1/1970"),SomeTable[time_val],HOUR)

 

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User
Super User

@Olifer 

Don't use the .date extension on the field.  That invokes the hidden auto time intelligence date field.  Try this.

duration = DATEDIFF(DateValue("1/1/1970"),SomeTable[time_val],HOUR)

 

@jdbuchanan71 

 

that did the trick! Thanks a lot for your quick support!

 

Best regards

Oliver

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.