Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 endti...
  • SivaMani's avatar
    SivaMani
    5 years ago

    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)

     

SivaMani's avatar
SivaMani
Icon for Resident Rockstar rankResident Rockstar
5 years ago

Anonymous, what is the data type of the columns?

 

shift_end_time - will only have the time without a date?

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

    • SivaMani's avatar
      SivaMani
      Icon for Resident Rockstar rankResident Rockstar
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

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