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

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi PattemManohar

         

        I tried the new formula but "BreakDuration" gave en error : DAX comparison values of type Date with values of type Text.

        Consider the VALUE or FORMAT function to convert one of the values.

         

        In my table, "date" column's data type is date. When i changed it's data type to text the formula didnt give an error but also didnt return any result.