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
Anonymous
Not applicable

Calculating Time/Hours Over Night

I have 2 columns, Activation Time (Time IN) + End Time (Time OUT). The goal is to calculate the time difference between Activation Time - End Time. 

 

CURRENT PROBLEM: The current output will take the literal difference between the 2 columns and not take into account if the time is crossing over from one day to the next. 

EX: In line 1 = Activation Time (10:08:53 PM) - End Time (2:04:40 PM) = -8hrs [Which is incorrect]

 

ANTICIPATED OUTPUT: The goal is to create calculated column which will take into account the date if the time crosses over from one day to the next. 

EX: Activation Time (10:08:53 PM) - End Time (2:04:40 PM) = 16hrs (15hrs 55mins)

 

Activation DateActivation TimeEndDateEndTimeTimeKey - ActiveTimeActiveEndDATEDIFFActiveEndTIMEDIFFTimeKey - EndTime
Wednesday, December 21, 201610:08:53 PMThursday, December 22, 20162:04:40 PM22081-81404
Wednesday, December 21, 201610:08:53 PMThursday, December 22, 20164:17:13 PM22081-61617
Wednesday, December 21, 201610:08:53 PMThursday, December 22, 20164:23:42 PM22081-61623

 

Below is my current DAX (which I know is not correct). Any advice will be greatly appreciated. 

 

timediff.png

1 ACCEPTED SOLUTION
Anonymous
Not applicable

The current problem is because there is no date part in the time stamp you are using to calculate the difference. 

Looks like your data source contains date and time in separate columns, so you need to create a custom column by combining date and time columns, for both Activation and End times.

 

Then you can use datediff function to calculate the time difference between the two time stamps.

Time Difference = DATEDIFF ( Activation_Date_Time, End_Date_Time, Hour)

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

The current problem is because there is no date part in the time stamp you are using to calculate the difference. 

Looks like your data source contains date and time in separate columns, so you need to create a custom column by combining date and time columns, for both Activation and End times.

 

Then you can use datediff function to calculate the time difference between the two time stamps.

Time Difference = DATEDIFF ( Activation_Date_Time, End_Date_Time, Hour)

 

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.