Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Overtime calculation

I am trying to calculate weekly over time of total Duration. The formula(s) that I am currently using are: TotalHours = SUM(HoursData[Duration]) Overtime = IF([TotalHours] > 37.5, IF(([TotalHo...
Need Help
MarkLaf's avatar
MarkLaf
Super User
3 years ago

I think something like this should work.

Overtime=
SUMX(
    HoursData,
    MAX( MIN( SUM( HoursData[Duration] ) - 37.5, 6.5 ), 0 )
)

Alternatively, this is the kind of thing that you could tackle with a calculated column.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi MarkLaf - The proposed solution did not work properly. It gave different numbers than to what I am expecting. But thank you

  • MarkLaf's avatar
    MarkLaf
    Super User
    3 years ago

    Oops. I shouldn't have had SUM in there. I think this should do it. If not, it would be helpful to provide some test data and more info on your model.

    Overtime=
SUMX(
    HoursData,
    MAX( MIN( HoursData[Duration] - 37.5, 6.5 ), 0 )
)