Overtime calculation

[mo1988]

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(([TotalHours]-37.5)>6.5, 6.5, ([TotalHours]-37.5)), 0)

While using the Overtime DAX Formula, it does calculate properly on the row level but when it comes to Matrix Total, its always showing 6.5

Any way to fix this please?

Thanks

[Padycosmos]

Hope this helps:

Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5,0)

[Padycosmos]

Hope this helps:

Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5,0)

[Padycosmos]

Hope this helps:

Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5<=0,0)

[mo1988]

Hi @Padycosmos - It says there is a syntax error

[Padycosmos]

Sorry about that, here's the corrected one

Hope this helps:

Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5,[TotalHours]-37.5, [TotalHours]-37.5<=0,0)

[mo1988]

Its still not calculating on the row level of the matrix .. 

[Padycosmos]

If the data is not sensitive, could you please share the pbix file?

[Padycosmos]

Please create a calculated column instead of Measure

[mo1988]

Even with the created column it says syntax error

[Padycosmos]

Really sorry, there was an extra bracket. I have removed it now. Please try again

Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5<=6.5,[TotalHours]-37.5, [TotalHours]-37.5<=0,0)

[mo1988]

It still didnt work - I am trying to share the dataset but couldnt find the option to do so. I am sorry

[Padycosmos]

You can put it in google drive and paste the link here

[Padycosmos]

Hope this edited one helps:

Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5<=0,0)

[Padycosmos]

Hope this helps:

Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5,0)

[MarkLaf]

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.

[mo1988]

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

[MarkLaf]

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