Calculating Overtime

[Anonymous]

I've done some research within the forum to look for the same question but couldn't find it. Please let me know if I've missed it though...

Anyways, I have these 2 tables, one with work hour each day. One is the date table. The relationship 'Hour'[EventDate] = 'DateTable'[Date] is created (many to one).

The mission is to calculate the WeeklyOvertime by summing all the hours each week and then minus 40 (Not minus 8 from each working day). Anything over 40hrs a week is considered Overtime.

I used matrix visualization. Collum is Week from 'DateTable' and Row is Person's names. Values is WeeklyOvertime (below)

I've tried the following function but so far no luck. 

WeeklyOvertime =

CALCULATE (

          SUM ('Hour'[Recorded hour];

          FILTER (

                  'Hour', 'Hour'[EventDate] <=MAX ('DateTable'[Date]))

          );

          FILTER (

                  'Hour', 'Hour'[EventDate] >=MIN ('DateTable'[Date]))

) - 40

Thank you in advanced for any suggestion you might have!

[Phil_Seamark]

Are you just after the amount over for each person for each week?

if so this might be close

Weekly Overtime = 
VAR 
	Overtime = SUM('Hour'[Recorded Hour])
VAR 
	ReturnValue = if(Overtime<> blank(),CALCULATE(Overtime) - 40,Blank())
RETURN 
	ReturnValue