Calculating overtime hours from cumulative value of week hours

[Tumbleweed]

Dear Community,

I need your help figuring out how to calculculate daily overtime hours.

1. Overime hours are hours exceeding 40 regular hours a week.

2. To find cumulative hours I used:

Cumulative Hours per Week =

SUMX(

FILTER(

Table,

Table[employee] = EARLIER(Table[employee]) &&

Table[Week] = EARLIER(Table[Week]) &&

Table[punch_start] <= EARLIER(Table[punch_start])

),

Table[total_hours])

3. To find regular hours I created calculated column:

Daily Regular = IF('Table[Cumulative Hours per Week] <= 40, 'Table'[total_hours], 40)

4. To find overtime:

Daily OT =

CALCULATE(

SUMX('Table',

IF('Table'[Cumulative Hours per Week] > 40,

'Table'[Cumulative Hours per Week]- 40,

0)))

BUT there is  a problem - overtime hours are shown accumulated and I need to show them per day. 

Can you please help me to figure it out?

 Thank you!

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result clearly.

[Tumbleweed]

Thank you for your help!

[lbendlin]

Daily overtime is calculated after reaching over 40 regular hours a week

BUT there is  a problem - overtime hours are shown accumulated and I need to show them per day. 

This is confusing to me. Can you please elaborate?

[Tumbleweed]

Employees work full week (Sunday - Saturday). All hours are overtime once they reach 40 hours of regular time. So, they get usually get overtime hours on Thursdays, Fridays, Saturdays. I need to show regular and overtime hours like this:

Thanks again for helping me with this problem. I really appreciate it.

[lbendlin]

Please correct your statement for point 4.

[Tumbleweed]

Thanks for pointing it out. I corrected it. Thanks for your help!

[lbendlin]

Your SUMX has to go over the individual days in the filter context.  To make it more obvious you can use SUMMARIZE first

SUMMARIZE (table, days, "hours worked",<hours worked>,"overtime", if <hours worked>>8, <hours worked>-8)

and then do a SUMX over <overtime>

[Tumbleweed]

The problem is that I cannot deduct 8 hours from total daily worked hours. Daily overtime is calculated after reaching over 40 regular hours a week. So, overtime hours would occur by the end of week only.

[Tumbleweed · Accepted solution]

I came up with the following DAX:

Daily Overtime Hours =

IF(

Table[Cumulative Hours per Week 1] > 40 && Table[Cumulative Overtime per Week] < Table[total_hours] , Table[Cumulative Hours per Week 1] - 40, 0) +

if(Table[Cumulative Hours per Week 1] > 40 && Table[Cumulative Overtime per Week] > Table[total_hours] , Table[total_hours], 0)

Thank you everyone for your help!