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 column that brings the latest event and then make a substraction but my 16 GB Ram computer can't perform the calculation since I have almost 53.000 records for the last month.

My question is if there is any way that I can measure the total break time having only a column with the time event, the event itself (In, Out).

 

 

Need Help

2 Replies

  • 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])