Forum Discussion
Format time - negative hour
Hello guys, I need help.
I have a database that considers the hours worked by the employees of a company and I am not able to work the data of negative hours from within Power bi.
He cannot even recognize the negative hours, nor the minus sign (-) as you can see in the image below.
This is the format show in power bi
The pictures are tables power query and excel, I need to make graphs to show the sum of positive and negative hours, is there no way to do this in power bi !? Can anyone help me deal with this issue ??
Thank you
1 Reply
Not totally clear on the logic your need, but you should be able to add a custom column in the query editor like this. This checks if the value in Column1 is before or after noon and then calculates the minutes between the start of the day or the end of the day. Returning negative numbers for times after noon. You could use Duration.TotalHours or Duration.TotalSeconds if you need different granularity.
= if Time.Hour([Column1])>12 then Duration.TotalMinutes([Column1] - #time(24,0,0)) else Duration.TotalMinutes([Column1]- #time(0,0,0))
Another option it to keep all your values positive but add another column in query to indicate if it is positive or negative. You can then write a measure to sum all the positive and subtract the sum of all the negative ones. But I would try the above way first.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
