topic Re: Calculating weekly overtime in DAX Commands and Tips

Calculating weekly overtime

abeirne — Fri, 18 Mar 2022 01:02:10 GMT

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

Re: Calculating weekly overtime

amitchandak — Fri, 18 Mar 2022 01:10:12 GMT

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

Re: Calculating weekly overtime

Anonymous — Wed, 23 Mar 2022 03:33:05 GMT

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

Re: Calculating weekly overtime

abeirne — Fri, 25 Mar 2022 17:22:56 GMT

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

Re: Calculating weekly overtime

abeirne — Fri, 25 Mar 2022 17:33:02 GMT

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]

)