Overtime Calculation

[barbforsman]

This question has been asked several times, but I wasn't able to make any of the solutions provided fit my scenario. I have a table with the following data:

 Name, Date Worked, WeekEnding Date, Hours Worked, Work Order

I need to sum the Hours Worked by week by employee to determine overtime.  I have used this formula for HoursPerWeek:

HoursPerWeek = calculate(
SUM(R550618NLK[Hours]),
filter(R550618NLK,R550618NLK[WeekEnding])
)

But finding Overtime is challenging me!  I have tried:

OT = if([HoursPerWeek]>40,[HoursPerWeek] - 40,BLANK())

This works when I look at individual employees, but this doesn't sum up correctly in a matrix.

I've also tried using my Measure, HoursPerWeek:

Total OT =
CALCULATE (
SUMX (
MsrTable,
IF (
[HoursPerWeek] > 40,
[HoursPerWeek] - 40,
BLANK()
)
)
)

But this gives me blank.

What is the correct formula?
Help will be much appreciated!

[barbforsman · Accepted solution]

Thank you!  I have posted the file here. 

[MarkS · Accepted solution]

Hi @barbforsman,

Could you try the OT formula as:

OT = if([HoursPerWeek]>40,[HoursPerWeek] - 40*[EmployeeCount],BLANK())

and see if that works in your scenario.

[MarkS · Accepted solution]

and see if that works in your scenario.

[barbforsman · Accepted solution]

Thank you!!  That works great!!