Calculate extra hours

[amaniramahi]

I have one table contains the following information for the start and end time of working hours of an employee

Date	Start Time	End Time
1-6-2020	8:35 AM	7:46 PM
2-6-2020	9:38 AM	6:03 PM
2-6-2020	8:48 AM	7:06 PM

and I have two what-if parameters

one for From and the other one for To

I choose the value of them based on the working hours

e.g I can change the starting working hour for that employee let's say from 8 AM and the end time for his working hours 5 PM

but these parameters contain only numbers (no time format)

how can I calculate the total extra hours for each day 

[mahoneypat · Accepted solution]

I put your example data in and made two What If parameters - StartTime and StopTime (with values from 0 to 24 hours).  If that is correct, you can use an expression like this to calculate the total overtime hours.  Note this subtracts the difference between the two parameters from the time difference for the shift each day.

Total Overtime =
VAR selectedduration = StopTime[StopTime Value] - StartTime[StartTime Value]
RETURN
SUMX (
Shift,
VAR overtime = ( Shift[End Time] - Shift[Start Time] ) * 24 - selectedduration
RETURN
IF ( overtime > 0, overtime, 0 )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amaniramahi]

Thank you..

I used your method but did a little adjustement

[harshnathani]

Hi @amaniramahi ,

Not sure why do you need WhatIf parameters.

To calculate Extra hrs you can create a measure

Extra hrs =

var shift_time = 9

RETURN

DATEDIFF(Max('Table'[Start Time]),MAX('Table'[End Time]),HOUR) - shift_time

Change the shift time as per requirement.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amaniramahi]

I think this does not give me the correct answer.

in rows, extra hours are 2,2, and 0

but the total is 1

how come?