Forum Discussion
Sum hours
Hello everyone!
I need to get the sum of hours in a given field and I know that the total sum that exceeds 24 hours .
I've tried using the code below , but it didn't work:
Column = (LEFT([HE];FIND(":";[HE])-1)*60+RIGHT([HE];LEN([HE])-FIND(":";[HE]))*1)/60
Where [HE] = hours
Can anyone help me?
Thank you in advance
Hi,
For example, you have a table like below, the “time” column is date type:
You can create a measure :
Measure = sumx('table', hour('table'[time])) + TRUNC(sumx('table', MINUTE('table'[time]))/60) & ":" & mod(sumx('table', MINUTE('table'[time])), 60) & ":00"
The result is what you want:
7 Replies
Hi,
For example, you have a table like below, the “time” column is date type:
You can create a measure :
Measure = sumx('table', hour('table'[time])) + TRUNC(sumx('table', MINUTE('table'[time]))/60) & ":" & mod(sumx('table', MINUTE('table'[time])), 60) & ":00"
The result is what you want:
Thanks for the solution. It worked fine for me, but I had to make a minor adjustment:
When it resulted rounded hours, the minutes showed up without a leading zero, such as "15:0:00", hence I adjusted it as below.
Duration =
sumx('table', hour('table'[time])) + TRUNC(sumx(Base_Pautas; MINUTE(Base_Pautas[Duração Real]))/60) & ":" &
mod(sumx('table', MINUTE('table'[time])); 60) &
IF(len(mod(sumx(Base_Pautas; MINUTE('table', MINUTE('table'[time])); 60))>1;":00";"0:00")
Hi Thiago
maybe this thread can help you
http://community.powerbi.com/t5/Desktop/Duration-Troubles/m-p/44560/highlight/true#M17204
Hi Vvelarde! Thanks for the answer!
It didn't work!
I tried to implement, but the result was not expected.
See example below, where the total should be 47 hours and 24 minutes. When I used your code, the result was almost 633 hours!
I believe it has some configuration that I have not learned to use..
HE
01:11:00
02:17:00
02:32:00
01:22:00
05:32:00
04:07:00
03:19:00
06:19:00
00:00:00
05:15:00
00:00:00
02:03:00
00:00:00
02:37:00
01:22:00
03:01:00
00:52:00
00:59:00
00:21:00
02:36:00
01:39:00
47:24:00
