Matrix sum daily over 8 hours and weekly over 40 hours

[omelo]

Hi PowerBI friends!

I'm calculating overtime which is calculated daily over 8hours and weekly over 40 hours. (Regular daily/weekly :7,5 and 37,5)

I have a matrix with a few columns:

employee number;employee name; transdate; hours worked,department, Daily>8

The daily>8 is a measure 

Daily>8 =

CALCULATE(

SUMX(

SUMMARIZE(

'EE Hours',

'EE Hours'[employee],

'EE Hours'[TotalHrs],

'EE Hours'[dept],

'EE Hours'[name],

'EE Hours'[transDate]

),

IF([TotalHrs]>8,[TotalHrs]-8,0)

)

)

So far all looks good and the problem starts here 😒

I need another column that shows a calculation of  weekly hours over 40
I've tried this  measure, have added a calendar table but can't get it to work.

Weekly>44 =

CALCULATE(

SUMX(

SUMMARIZE(

'EE Hours',

'EE Hours'[employee],

'EE Hours'[TotalHrs],

'EE Hours'[dept],

'EE Hours'[name],

'EE Hours'[Weeknum]

),

IF([TotalHrs]>40,[TotalHrs]-40,0)

)

)

I would appreciate any assistance.

Thank you

[V-lianl-msft · Accepted solution]

Could you provide a sample pbix after deleting sensitive data for us to do more tests

[V-lianl-msft · Accepted solution]

Could you provide a sample pbix after deleting sensitive data for us to do more tests

[omelo · Accepted solution]

I came across this post by Alberto Ferrari

Obtaining accurate totals in DAX - SQLBI

This solved my issue.

Thanks Alberto and thanks to others that responded

[omelo]

Hi Gabriel,  I've edited the 2nd. measure to fix the error I've made when writting the post and to add the weeknum instead of date. I should say that the result of the measure is zero, like it is not considering the "grouping" by week.

Weekly>44 =

CALCULATE(

SUMX(

SUMMARIZE(

'EE Hours',

'EE Hours'[employee],

'EE Hours'[TotalHrs],

'EE Hours'[emorg],

'EE Hours'[name],

'EE Hours'[Weeknum]

),

IF([TotalHrs]>40,[TotalHrs]-40,0)

)

)

This one is proven to be a complicated. Perhaps, this is not possible.
Thank you for your response.

[Gabriel_Walkman]

Looks like a game of "spot the differences". Your latter measure is missing the first two rows of the first measure, and one comma from the middle.

I must say I've never used summarize before. For the first case, I'd just create a new dax or power query column, ie. a dax version:
overtime = max( [TotalHrs]-8, 0 )
and pull that to my visual where a daily overtime sum is needed.

Not sure how I'd make the latter though.