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

 

DateStart TimeEnd Time
1-6-20208:35 AM7:46 PM
2-6-20209:38 AM6:03 PM
2-6-20208:48 AM7: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 

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

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    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

    • amaniramahi's avatar
      amaniramahi
      Helper V
      6 years ago

      Thank you..

      I used your method but did a little adjustement

  • 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?