Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Calculating Overtime

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!

 

Need Help

1 Reply

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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