Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

time difference between timestamps except first and last login

I have the below data table that containts users logs. After the substraction between last log out and First log in I have found the total working hours duration. I have tried to add a calculated col...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous,

 

What do you mean "measure the total break time having only a column with the time event, the event itself (In, Out)"? What is the current measure formula you used?

 

Regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    there is the event timestamp column that records each event (direction column). For each day a user by hitting his/her card a new event is being recorded. I want to measure the total time he/she spent on break (difference out - in and ignore check) per day during his/her working hours. The right picutre shows the total working hours per day and it measures Last out - First In

    (

    # Working Hours Seconds = IF(
    HASONEVALUE(Dates[Date]),
    (IF(
    HASONEVALUE(Data[User]),
    [Last hit]-[First hit],
    SUMX(VALUES(Data[User]),[Last hit]-[First hit]))),
    SUMX(VALUES(Dates[Date]),(IF(
    HASONEVALUE(Data[User]),
    [Last hit]-[First hit],
    SUMX(VALUES(Data[User]),[Last hit]-[First hit])))))*86400

    )

     

    * Last hit = MAX(Data[Event Time stamp]) 

    First hit = MIN(Data[Event Time stamp])