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

difference between two time stamps

Hi Community, 

 

Please help with finding a duration between two time stamps.  

 

I have the 3 columns, i.e., shift_date, shift_start_time, shift_end_time.  As this is an evening shift, the endtime mostly falls the next day.

 

shift_dateshift_start_timeshift_end_timeworking_hours
5-Apr-216:00:00 PM

2:30:00 AM

16

 

I tried using 

working_hours= DATEDIFF( 'table_1'[shift_start_time], 'table1'[shift_end_time], HOUR),

but it is not working for me.  

 

Any reference to documents is also appreciated.

 

Thanks and Regards

Sabyasachi

1 ACCEPTED SOLUTION

@Anonymous,

 

This would work, 

WorkingHrs =
VAR __StartTime = MAX('Table (2)'[shift_date]) + MAX('Table (2)'[shift_start_time])
VAR __EndTime = IF(MAX('Table (2)'[shift_start_time])>MAX('Table (2)'[shift_end_time]),MAX('Table (2)'[shift_date])+1+MAX('Table (2)'[shift_end_time]),MAX('Table (2)'[shift_date])+MAX('Table (2)'[shift_end_time]))
RETURN
DIVIDE(DATEDIFF(__StartTime,__EndTime,MINUTE),60)

 

View solution in original post

7 REPLIES 7
SivaMani
Resident Rockstar
Resident Rockstar

@Anonymous, what is the data type of the columns?

 

shift_end_time - will only have the time without a date?

 

 

Anonymous
Not applicable

Thank yo @SivaMani for looking into this.  Here, data type is time (h:nn:ss AM/PM).  Yes, the shift end time without a date, its always either the shift day or next day.

 

Regards

Sabyasachi

@Anonymous,

 

Try the below measure,

WorkingHrs =
VAR __StartTime = MAX('Table (2)'[shift_date]) + MAX('Table (2)'[shift_start_time])
VAR __EndTime = IF(MAX('Table (2)'[shift_start_time])>MAX('Table (2)'[shift_end_time]),MAX('Table (2)'[shift_date])+1+MAX('Table (2)'[shift_end_time]),MAX('Table (2)'[shift_date])+MAX('Table (2)'[shift_end_time]))
RETURN
DATEDIFF(__StartTime,__EndTime,HOUR)
Anonymous
Not applicable

@SivaMani I am getting the output as rounded value even when we expect a fraction.1.jpg

@Anonymous,

 

This would work, 

WorkingHrs =
VAR __StartTime = MAX('Table (2)'[shift_date]) + MAX('Table (2)'[shift_start_time])
VAR __EndTime = IF(MAX('Table (2)'[shift_start_time])>MAX('Table (2)'[shift_end_time]),MAX('Table (2)'[shift_date])+1+MAX('Table (2)'[shift_end_time]),MAX('Table (2)'[shift_date])+MAX('Table (2)'[shift_end_time]))
RETURN
DIVIDE(DATEDIFF(__StartTime,__EndTime,MINUTE),60)

 

Anonymous
Not applicable

@SivaMani awesome, thanks a lot

@Anonymous, You're welcome! I am glad that it worked.

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.

Top Solution Authors