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

     

12 Replies

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

      While I was trying to implement above logic getting below error.

       

      "The syntax for ')' is incorrect. (DAX(VAR _InTime = CALCULATE(MAX('Raw Data'[Time]),FILTER(ALL('Raw Data'[Time]),'Raw Data'[Time] < EARLIER('Raw Data'[Time]) && 'Raw Data'[Description #2] = EARLIER('Raw Data'[Description #2]) && 'Raw Data'[Description #1] <> EARLIER('Raw Data'[Description #1]) && CONTAINS('Raw Data','Raw Data'[Description #1]," EXIT")) "

       

      Could you please help me out here.

       

      Thanks

      Siva