Weekly Hours Worked Calculation

[vandelD]

I have a data set that brings in each employee's daily hours worked, split into regular and overhead. I have calculations to sum hours worked in a day, week, etc., but am now trying to create a calculation that only sums hours that are regular when exceeding 40 in a given week. I have created a custom column that provides the Start of Week based on the transaction date, which allows for me to build visuals looking at hours on a weekly basis. But in addition to viewing total regular hours, I want to a sum of only those hours over 40 in a given timeframe.

Here is an example of my data set. For the Start of Week 11/5/2023, this employee has 42 RegHrs, in the week of 11/12 has 42 RegHrs again, and in the week of 11/19 has 36. In this example, the calculation should provide 4 hours over 40 regular hours. Is this possible? Thanks in advance for any assistance.

Employee	TransDate	RegHrs	OHHours	StartOfWeek
2610	11/6/2023	8.25	2.5	11/5/2023
2610	11/7/2023	8.5	1.5	11/5/2023
2610	11/8/2023	8.25	1	11/5/2023
2610	11/9/2023	9	0.5	11/5/2023
2610	11/10/2023	8	0.5	11/5/2023
2610	11/13/2023	8.5	1	11/12/2023
2610	11/14/2023	8.5	0	11/12/2023
2610	11/15/2023	7.75	1.5	11/12/2023
2610	11/16/2023	9	0.5	11/12/2023
2610	11/17/2023	8.25	1	11/12/2023
2610	11/20/2023	9	1	11/19/2023
2610	11/21/2023	9.5	0.5	11/19/2023
2610	11/22/2023	8.5	0.5	11/19/2023
2610	11/23/2023	0	8	11/19/2023
2610	11/24/2023	9	1.25	11/19/2023

[ryan_mayu · Accepted solution]

@vandelD 

you can try this

Measure = 
var tbl=SUMMARIZE('Table','Table'[Employee],'Table'[StartOfWeek],"hour",sum('Table'[RegHrs])-40)
return sumx(FILTER(tbl,[hour]>0),[hour])

[vandelD]

This is perfect, thank you!

[ryan_mayu]

you are welcome