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 

 

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.

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

      Anonymous I just copied your sample data and the data types are detected automatically, here is the datatypes I've

       

      WorkedID - Whole Number

      Direction - Text

      Date - Date

      Time - Time

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi PattemManohar, it is my fault. Now it works.  

         

        i have checked the results and calculation still crashes for spesific days.

         

        Previous date's "afrer-midnight" records are effecting the calculation.

         

        You can analysis below record for example.  It starts with an OUT record but actually it is previous date's movement. 

         

        It i s a hard scanario to cover %100.  Is there any idea to eliminate these OUT records ?

         

        may be we can include next day's first "after- midnight OUT record" to calculation of calculated day? And disregard other records before 06:00 ? 

         

        Worker IDDirectionDateTimeBreakDuration
        1OUT10.08.201803:39:04 
        1IN10.08.201808:43:26304
        1OUT10.08.201813:22:01 
        1IN10.08.201814:33:5071
        1OUT10.08.201818:12:56 
             
             
        Worker IDDirectionDateTimeBreakDuration
        1OUT15.08.201800:49:41 
        1IN15.08.201811:42:49653
        1OUT15.08.201812:39:35 
        1IN15.08.201813:34:5555
        1OUT15.08.201817:56:34