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.
Hi folks. I’m trying to figure out a solution to calculate hours worked on our time sheets. We have an entry for each day, with an in time and an out time on a 24-hour clock. The issue I’m having is occasionally people are required to work a shift from before midnight to after midnight. So, if for example, someone clocks in at 23:00 and clocks out at 1:00, PowerBI calculates that as negative 22 hours. Is there some kind of custom column I could create to get the right number of hours?
Any ideas would be appreciated.
Thanks.
Solved! Go to Solution.
Thank you both for your suggestions. What eneded up working for me was using a custom colmn
Duration.TotalMinutes(#time(0,0,0)+([End Time]-[Begin Time])-#time(0,0,0))
I found this answer on another post on here.
https://community.powerbi.com/t5/Desktop/Negative-Duration/m-p/250515#M111195
Thank you both for your suggestions. What eneded up working for me was using a custom colmn
Duration.TotalMinutes(#time(0,0,0)+([End Time]-[Begin Time])-#time(0,0,0))
I found this answer on another post on here.
https://community.powerbi.com/t5/Desktop/Negative-Duration/m-p/250515#M111195
Hi Sean2,
You could try below measure to see whether it work or not.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ31DdUMLQyMAAiJR2EkJExVCxWJxqLqA6mZhSFhgboCo0UTIlVaAi3OxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [st = _t, et = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"st", type datetime}, {"et", type datetime}})
in
#"Changed Type"
Measure = DATEDIFF(MIN('Table'[st]),MIN('Table'[et]), HOUR)
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Or you could just add a custom column which checks if the start is greater than end.
Using the sample data from Zoe's post the custom column expression would be something like:
if [st] > [et] then Duration.Hours([st] -[et]) else Duration.Hours([et] -[st])
In this case I think you need to use a datetime column, not just the time component when calculating the hours.
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 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |