Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you..
I used your method but did a little adjustement
Hi @amaniramahi ,
Not sure why do you need WhatIf parameters.
To calculate Extra hrs you can create a measure
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |