Calculating weekly overtime

[abeirne]

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

[v-cgao-msft]

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

[amitchandak · Accepted solution]

@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]

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]

)

[abeirne]

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