Forum Discussion

amaniramahi's avatar
amaniramahi
Helper V
6 years ago
Solved

Calculate extra hours

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:...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    amaniramahi
    Helper V
    6 years ago

    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?