Overtime Dax Formulas

[Adams_Apple]

Hi All,

I know there are already a lot of posts on this topic, but I have scoured them all and can't seem to get this to work.
I need to be able to determine overtime hours per week per employee, as well as overtime hours for groups as a whole, and then eventuall use that to calculate overtime costs.

I can successfully calculate Overtime Hours with this measure, but it only works in a table where I am looking at employees by weeks. if i look at more than 1 employee or more than 1 week I run into a sum total issue. 

Overtime Hours =

IF(

(CALCULATE(SUMX(Employees,Employees[Hours Paid]), FILTER(Employees,Employees[Year] && Employees[Week of Year]))) > 40,

(CALCULATE(SUMX(Employees,Employees[Hours Paid]), FILTER(Employees,Employees[Year] && Employees[Week of Year]))) - 40,

0)

This measure appears to have be calculating the sum total, but for normal hours and overtime hours, it also doesnt display the hours per employee for some reason.

Measure = SUMX(

    SUMMARIZE( Employees,

        Employees[Hours Paid],

        "Value", IF(SUM(Employees[Hours Paid]) > 40, SUM(Employees[Hours Paid]) - 40, 0)

    ),

    [Value]

)

And then the sum total issues go on to effect the overtime pay..

Thank you so much for your help!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Work Hours: = 
SUM( work_hours[work_hours] )

Overtime Hours: = 
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( work_hours, 'calendar'[Start of Week], employee[name] ),
        "@overtime",
            IF ( [Work Hours:] > 40, [Work Hours:] - 40, 0 )
    )
RETURN
    SUMX ( _t, [@overtime] )

Overtime Pay: = 
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( work_hours, 'calendar'[Start of Week], employee[name], employee[overtime_rate] ),
        "@overtimepay",
            IF ( [Work Hours:] > 40, [Work Hours:] - 40, 0 ) * employee[overtime_rate]
    )
RETURN
    SUMX(_t, [@overtimepay])

[Adams_Apple]

Absolutely awesome, thank you so much!!!!