Forum Discussion

barbforsman's avatar
barbforsman
Resolver I
8 years ago
Solved

Overtime Calculation

This question has been asked several times, but I wasn't able to make any of the solutions provided fit my scenario. I have a table with the following data:  Name, Date Worked, WeekEnding Date, Hour...
Need Help
  • barbforsman's avatar
    barbforsman
    8 years ago

    Thank you!  I have posted the file here. 

  • MarkS's avatar
    MarkS
    8 years ago

    Hi barbforsman,

    Could you try the OT formula as:

     

    OT = if([HoursPerWeek]>40,[HoursPerWeek] - 40*[EmployeeCount],BLANK())

     
    and see if that works in your scenario.
  • barbforsman's avatar
    barbforsman
    8 years ago

    Thank you!!  That works great!!

  • MarkS's avatar
    MarkS
    8 years ago

    hi barbforsman,

    This measure also seems to work

    OTHours =
CALCULATE (
    SUMX (
        SUMMARIZE (
            R550618NLK,
            [WeekEnding],
            [Name],
            "Overtime", MAX ( [HoursPerWeek] - 40, 0 )
        ),
        [Overtime]
    ),
    ALL ( R550618NLK[Pay Type] )
)

    however I would also check it, especially if an employee job title could change during the week.

MarkS's avatar
MarkS
Resolver IV
8 years ago

Hi barbforsman

 

Possibly try this Calculated Column:

OT Hours =
DIVIDE (
    MAX (
        CALCULATE (
            SUM ( R550618NLK[Hours] ),
            ALLEXCEPT ( R550618NLK, R550618NLK[Name], R550618NLK[WeekEnding] )
        )
            - 40,
        0
    ),
    CALCULATE (
        COUNT ( R550618NLK[Hours] ),
        ALLEXCEPT ( R550618NLK, R550618NLK[Name], R550618NLK[WeekEnding] )
    )
)

and see if this will work for you.

MarkS's avatar
MarkS
Resolver IV
8 years ago

hi barbforsman,

This measure also seems to work

OTHours =
CALCULATE (
    SUMX (
        SUMMARIZE (
            R550618NLK,
            [WeekEnding],
            [Name],
            "Overtime", MAX ( [HoursPerWeek] - 40, 0 )
        ),
        [Overtime]
    ),
    ALL ( R550618NLK[Pay Type] )
)

however I would also check it, especially if an employee job title could change during the week.