Forum Discussion

Adams_Apple's avatar
Adams_Apple
Frequent Visitor
1 year ago
Solved

Overtime Dax Formulas

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!

 


 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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])

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    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])