Forum Discussion
Solved
Negative hours
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 ...
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])
