Forum Discussion

alevandenes's avatar
alevandenes
Icon for Helper IV rankHelper IV
2 years ago
Solved

calculate duration

Hi community,   i would like to calculate the duration of each clock in-clock out, considering that: - Start and Stop rows will not follow each other most of the time - there might be more clock ...
Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi alevandenes ,

     

    Assuming the id column corresponds to the order of timestamps, you first need to calculate the start of each stop time where teamMember is equal to the current row teamMember and [id] is < the current row [id] and deduct that from stop. You can then sum the result of a calculated column and show it in a visual. 

     

    Pleasae see attached pbix for details.

     

danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

Hi alevandenes ,

 

Can you please post your sample data as a text that anyone can copy paste as a table in Exel and not an image? Also to confirm, the duration is ids 5-4, 6-5 etc ?

  • alevandenes's avatar
    alevandenes
    Icon for Helper IV rankHelper IV
    2 years ago
    identryTypetimeStampteamMember
    4Start2023-10-16 13:19:45.62812382
    5Stop2023-10-16 13:21:10.79312382
    6Start2023-10-16 13:21:40.15712382
    7Stop2023-10-16 13:21:44.8812382
    8Start2023-10-16 13:22:28.31316230
    9Stop2023-10-16 13:23:24.24216230
    10Start2023-10-16 13:24:27.73912073
    11Start2023-10-16 13:26:05.89316230
    12Stop2023-10-16 13:26:23.47312073
    13Start2023-10-16 13:28:49.52212382
    14Stop2023-10-16 13:29:36.05312382
    15Start2023-10-16 13:29:48.39212382
    16Start2023-10-16 13:30:00.59612382
    17Stop2023-10-16 13:30:12.30912382
    18Start2023-10-16 13:30:16.38412382
    19Stop2023-10-16 13:30:57.83712382
    20Start2023-10-16 13:31:17.91412382
    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      2 years ago

      Hi alevandenes ,

       

      Assuming the id column corresponds to the order of timestamps, you first need to calculate the start of each stop time where teamMember is equal to the current row teamMember and [id] is < the current row [id] and deduct that from stop. You can then sum the result of a calculated column and show it in a visual. 

       

      Pleasae see attached pbix for details.

       

      • alevandenes's avatar
        alevandenes
        Icon for Helper IV rankHelper IV
        2 years ago

        amazing! thanks a lot!