Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

calculating working time in office

Hi all,    I would like to calculate the elapsed time in office by whole day for each worker ID.    How can i calculate this without the time spended at outside   thnx  
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Anonymous Please try this as a "New Column" which will give the each breaktime difference between OUT and IN time

     

    BreakDuration = 
VAR _InTime = CALCULATE(MAX(Test95BreakTime[Time]),FILTER(ALL(Test95BreakTime),Test95BreakTime[Time] < EARLIER(Test95BreakTime[Time]) && Test95BreakTime[WorkerID] = EARLIER(Test95BreakTime[WorkerID]) && Test95BreakTime[Direction] <> EARLIER(Test95BreakTime[Direction]) && Test95BreakTime[Direction] = "OUT"))
RETURN DATEDIFF(TIME(HOUR(_InTime),MINUTE(_InTime),SECOND(_InTime)),Test95BreakTime[Time],MINUTE)

     

    Then, you can create a measure to get the total worked/logged time excluding the break time using the below as "Measure"

     

    Test95TotalLoggedTime = 
VAR _BreakTime = SUM(Test95BreakTime[BreakDuration])
VAR _FirstIn = CALCULATE(MIN(Test95BreakTime[Time]),Test95BreakTime[Direction]="IN")
VAR _LastOut = CALCULATE(MAX(Test95BreakTime[Time]),Test95BreakTime[Direction]="OUT")
RETURN DATEDIFF(_FirstIn,_LastOut,MINUTE)-_BreakTime

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Works great! Pattem, I realy appreciate your support

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi PattemManohar, 

 

I have a bug. How can i add a filter for multiple days? In some cases moethod crqashes and it starts to calculate from other days recors. I think it has a relation with exits and entrances after-midnight. 

 

Thanks 

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous Please post the sample data in copiable format to replicate the issue that you are facing.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi PattemManohar 

       

      Please find the sample data below. Thnx 

       

      Worker IDDirectionDateTimeBreakDuration
      1IN1.08.201808:29:24290
      1OUT1.08.201812:34:02 
      1IN1.08.201813:04:0225
      1OUT1.08.201817:52:38 
      1IN2.08.201808:21:21282
      1OUT2.08.201812:09:40 
      1IN2.08.201812:17:236
      1OUT2.08.201812:33:44 
      1IN2.08.201812:36:151
      1OUT2.08.201819:37:12 
      1IN3.08.201809:48:2860
      1OUT3.08.201813:27:17 
      1IN3.08.201813:51:5221
      1OUT3.08.201814:51:06 
      1IN3.08.201815:15:0924
      1OUT3.08.201817:00:56 
      1IN3.08.201817:03:413
      1OUT3.08.201820:25:34 
      1IN9.08.201809:07:3319
      1OUT9.08.201813:11:37 
      1IN9.08.201813:29:492
      1OUT9.08.201816:46:00 
      1IN9.08.201817:00:002
      1OUT9.08.201817:56:45 
      1IN9.08.201818:12:5416
      1OUT10.08.201803:39:04 
      1IN10.08.201808:43:26304
      1OUT10.08.201813:22:01 
      1IN10.08.201814:33:5063
      1OUT10.08.201818:12:56 
      1IN13.08.201808:59:2511
      1OUT13.08.201811:30:52 
      1IN13.08.201811:53:4923
      1OUT13.08.201813:30:24 
      1IN13.08.201814:04:4334
      1OUT13.08.201817:56:58 
      1IN14.08.201808:33:53294
      1OUT14.08.201808:48:38 
      1IN14.08.201808:57:359
      1OUT14.08.201810:27:31 
      1IN14.08.201810:35:072
      1OUT14.08.201812:11:22 
      1IN14.08.201812:42:013
      1OUT14.08.201817:17:12 
      1IN14.08.201818:52:2240
      1OUT15.08.201800:49:41 
      1IN15.08.201811:42:4912
      1OUT15.08.201812:39:35 
      1IN15.08.201813:34:554
      1OUT15.08.201817:56:34 
      1IN16.08.201809:37:5549
      1OUT16.08.201810:33:37 
      1IN16.08.201810:50:1117
      1OUT16.08.201812:35:54 
      1IN16.08.201813:33:133
      1OUT16.08.201816:58:34 
      1IN16.08.201817:12:0712
      1OUT16.08.201817:41:25 
      1IN17.08.201808:26:00287
      1OUT17.08.201813:10:10 
      1IN17.08.201813:29:222
      1OUT17.08.201817:26:58 
      2IN1.08.201809:46:4320
      2OUT1.08.201809:54:09 
      2IN1.08.201810:07:5913
      2OUT1.08.201811:29:28 
      2IN1.08.201811:41:401
      2OUT1.08.201812:01:26 
      2IN1.08.201813:04:4459
      2OUT1.08.201815:13:26 
      2IN1.08.201815:26:096
      2OUT1.08.201816:42:35 
      2IN1.08.201817:03:512
      2OUT1.08.201818:08:12 
      2IN1.08.201818:36:384
      2OUT1.08.201819:49:22 
      2IN2.08.201809:15:25542
      2OUT2.08.201809:26:01 
      2IN3.08.201810:30:1636
      2OUT3.08.201810:54:56 
      2IN3.08.201811:07:0013
      2OUT3.08.201811:59:24 
      2IN3.08.201813:35:4590
      2OUT3.08.201815:02:07 
      2IN3.08.201815:23:193
      2OUT3.08.201817:34:40 
      2IN6.08.201810:28:2634
      2OUT6.08.201810:45:56 
      2IN6.08.201811:18:1011
      2OUT6.08.201811:56:59 
      2IN6.08.201813:16:5671
      2OUT6.08.201814:58:05 
      2IN6.08.201816:21:584
      2OUT6.08.201817:58:42 
      2IN6.08.201818:29:3821
      2OUT6.08.201818:32:36 
      2IN7.08.201810:10:4416
      2OUT7.08.201810:47:15 
      2IN7.08.201811:07:2013
      2OUT7.08.201811:55:25 
      2IN7.08.201813:18:2473
      2OUT7.08.201814:59:45 
      2IN7.08.201815:17:314
      2OUT7.08.201816:17:52 
      2IN7.08.201816:50:348
      2OUT7.08.201817:29:05 
      2IN7.08.201818:00:152
      2OUT7.08.201818:32:13 
      2IN8.08.201809:20:281
      2OUT8.08.201809:26:29 
      2IN8.08.201809:40:5814
      2OUT8.08.201810:33:57 
      2IN8.08.201811:04:0610
      2OUT8.08.201811:40:19 
      2IN8.08.201811:54:324
      2OUT8.08.201811:58:39 
      2IN8.08.201813:23:3878
      2OUT8.08.201814:34:47 
      2IN8.08.201814:57:307
      2OUT8.08.201816:00:47 
      2IN8.08.201816:14:2614
      2OUT8.08.201817:41:22 
      2IN9.08.201809:40:5414
      2OUT9.08.201809:52:42 
      2IN9.08.201810:28:1634
      2OUT9.08.201811:50:02 
      2IN9.08.201813:26:0281
      2OUT9.08.201815:01:54 
      2IN9.08.201815:24:084
      2OUT9.08.201817:01:53 
      2IN9.08.201817:21:3514
      2OUT9.08.201817:47:17 
      2IN10.08.201809:24:515
      2OUT10.08.201809:47:57 
      2IN10.08.201810:59:485
      2OUT10.08.201811:56:21 
      2IN10.08.201811:57:011
      2OUT10.08.201811:57:57 
      2IN10.08.201813:45:16100
      2OUT10.08.201815:07:24 
      2IN10.08.201815:38:595
      2OUT10.08.201817:07:10 
      2IN13.08.201810:19:1225
      2OUT13.08.201810:54:16 
      2IN13.08.201811:44:374
      2OUT13.08.201811:58:21 
      2IN13.08.201813:13:0468
      2OUT13.08.201814:50:23 
      2IN13.08.201815:26:016
      2OUT13.08.201816:30:29 
      2IN13.08.201816:58:417
      2OUT13.08.201817:47:27 
      2IN14.08.201810:20:5026
      2OUT14.08.201811:07:27 
      2IN14.08.201811:32:243
      2OUT14.08.201811:57:32 
      2IN14.08.201813:19:0974
      2OUT14.08.201815:20:47 
      2IN14.08.201815:44:2511
      2OUT14.08.201816:51:47 
      2IN14.08.201817:06:595
      2OUT14.08.201817:56:14 
      2IN14.08.201818:09:341
      2OUT14.08.201818:48:19 
      2IN14.08.201819:02:3314
      2OUT14.08.201819:12:59 
      2IN15.08.201810:16:4322
      2OUT15.08.201810:54:20 
      2IN15.08.201811:18:2511
      2OUT15.08.201812:05:07 
      2IN15.08.201813:26:1381
      2OUT15.08.201815:08:55 
      2IN15.08.201815:42:219
      2OUT15.08.201817:28:03 
      2IN15.08.201817:52:325
      2OUT15.08.201819:28:30 
      2IN16.08.201809:14:14541
      2OUT16.08.201809:19:20 
      2IN16.08.201809:43:5517
      2OUT16.08.201810:37:25 
      2IN16.08.201810:47:390
      2OUT16.08.201812:01:37 
      2IN16.08.201813:05:4060
      2OUT16.08.201815:33:31 
      2IN16.08.201815:58:0525
      2OUT16.08.201816:14:45 
      2IN16.08.201816:29:4212
      2OUT16.08.201816:42:29 
      2IN17.08.201800:10:52 
      2OUT17.08.201800:13:28 
      • PattemManohar's avatar
        PattemManohar
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Anonymous Thanks for providing the sample data.

         

        Here is the new logic to handle mutliple dates,

         

        Add a new column as below (Added an another condition to check the date as well)

         

        BreakDuration = 
VAR _InTime = CALCULATE(MAX(Test95BreakTime1[Time]),FILTER(ALL(Test95BreakTime1),Test95BreakTime1[Time] < EARLIER(Test95BreakTime1[Time]) && Test95BreakTime1[WorkerID] = EARLIER(Test95BreakTime1[WorkerID]) && Test95BreakTime1[Direction] <> EARLIER(Test95BreakTime1[Direction]) && Test95BreakTime1[Date] = EARLIER(Test95BreakTime1[Date]) && Test95BreakTime1[Direction] = "OUT"))
RETURN DATEDIFF(TIME(HOUR(_InTime),MINUTE(_InTime),SECOND(_InTime)),Test95BreakTime1[Time],MINUTE)

        Create a measure as below

         

        Test95TotalLoggedTime1 = 
VAR _BreakTime = SUM(Test95BreakTime1[BreakDuration])
VAR _FirstIn = FORMAT(MIN(Test95BreakTime1[Time]),"hh:mm:ss")
VAR _LastOut = FORMAT(MAX(Test95BreakTime1[Time]),"hh:mm:ss")
RETURN DATEDIFF(_FirstIn,_LastOut,MINUTE)-_BreakTime