Forum Discussion
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(([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
17 Replies
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.
Hi MarkLaf - The proposed solution did not work properly. It gave different numbers than to what I am expecting. But thank you
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 ) )
Hope this helps:
Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5,0)
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)
Please create a calculated column instead of Measure
If the data is not sensitive, could you please share the pbix file?
Hope this helps:
Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5,0)
Hope this helps:
Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5<=0,0)
Hi Padycosmos - It says there is a syntax error
Hope this helps:
Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5,0)
