The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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:
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
Seems quite possible there is a better way to do this, but... I do think this will "work"
MyMeasure = 24 * sumx(Table1, round(Table1[MyTime],99))
MyTime was a DateTime type for me (not a string), and I'm just using round to force it into a floating point number (where 1.0 is a full day).
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the July 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
112 | |
69 | |
48 | |
43 |
User | Count |
---|---|
188 | |
87 | |
77 | |
74 | |
60 |