Calculating Over-Time Hours

[SimonNU]

I'm trying to calculate a measure that will return weekly over-time. Assuming overtime is 50 hours per week, how would I do it using the below model?

Fct_Hours:
Date_Key | Hours

Dim_Date:
Date_Key | Date | Month | Year | WeekOfYear (e.g. "Week 3, 2015") | etc..


Essentially, I'd like the Sum of Hours - Over Time Hours but only where that week's total hours is greater than 50.


Thanks!

 

[Ozeroth]

A measure like this should do the trick:

=
SUMX (
    VALUES ( Dim_Date[WeekOfYear] ),
    IF (
        CALCULATE ( SUM ( Fct_Hours[Hours] ) ) > 50,
        CALCULATE ( SUM ( Fct_Hours[Hours] ) ) - 50
    )
)

 

[SimonNU]

Great, thanks Ozeroth!

For anyone reading this, I've expanded on Ozeroth's solution to develop an over-time calculation on a weekly and employee level, i.e. sum hours over 50 for each employee and for each week. This one is more likely to be encountered:

Overtime Hours :=
SUMX (
    VALUES ( Dim_Date[WeekOfYear] ),
    SUMX (
        VALUES ( Fct_Hours[Employee Name] ),
        IF (
            CALCULATE ( SUM ( Fct_Hours[Hours] ) ) > 50,
            CALCULATE ( SUM ( Fct_Hours[Hours] ) ) - 50
        )
    )
)