Forum Discussion

abeirne's avatar
abeirne
Helper II
4 years ago
Solved

Calculating weekly overtime

Hi I am looking to calculate over time for a group of employees. I have my hours worked, a caluclated column which is
DIVIDE((('Employee_Time'[Clock Out Date] - 'Employee_Time'[Clock in Date] * 1,440) , 60 ).
I have tried some measures and calculated columns using 

IF(
      [Hours Worked ] > 40,
      - 40,
      0
)

But this does not work. I just need to know the correct formula for an ot calculation. I feel like it should be filtered by pay period or payroll date, but I am not sure how to do that.  I have a Payroll Date  with columns [Date] , [pay period], and [weekday]. Thanks

  • amitchandak's avatar
    amitchandak
    4 years ago

    abeirne , Try a measure like

     


    new column = datediff('Employee_Time'[Clock in Date], 'Employee_Time'[Clock Out Date],second)/3600.0

    new measure = sumx(addcolumns(summarize(Table, Table[Emp], Table[Week num], "_1", sum(Table[New column])), [_2], if([_1]>40, [_1]-40,blank())),[_2])

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    abeirne , Try a measure like

     


    new column = datediff('Employee_Time'[Clock in Date], 'Employee_Time'[Clock Out Date],second)/3600.0

    new measure = sumx(addcolumns(summarize(Table, Table[Emp], Table[Week num], "_1", sum(Table[New column])), [_2], if([_1]>40, [_1]-40,blank())),[_2])

    • abeirne's avatar
      abeirne
      Helper II
      4 years ago

      The column is a lot easier, thank you. Would you be able to explain the measure for me a little more? I don't understand the _2 part.

      Thank you

    • abeirne's avatar
      abeirne
      Helper II
      4 years ago
      I don't understand what the [_2] is supposed to be? Thanks

      Measure =
      SUMX(
      ADDCOLUMNS(
      SUMMARIZE(
      'dds Employee_Time_Tb', 'Payroll Date'[Week Num], "_1", SUM('dds Employee_Time_Tb'[Hours Worked 2])),
      [_2],
      IF(
      [_1] > 40,
      [_1] - 40,
      BLANK())),
      [_2]
      )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi abeirne ,

     

    Have you resolved the issue? If yes, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from the thread. If not, please consider sharing more details about it. And it would be great if there was a sample file without any sensitive information here.

     

    Best Regards,
    Community Support Team_Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data