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, a...
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])

     

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

 

  • Adams_Apple's avatar
    Adams_Apple
    Frequent Visitor
    1 year ago

    Absolutely awesome, thank you so much!!!!