Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Thiago
Regular Visitor

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

 

 

1 ACCEPTED SOLUTION
AlexChen
Employee
Employee

Hi, 

 

For example, you have a table like below, the “time” column is date type:

 

1.png

 

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:

 

2.png

 

 

 

 

View solution in original post

7 REPLIES 7
AlexChen
Employee
Employee

Hi, 

 

For example, you have a table like below, the “time” column is date type:

 

1.png

 

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:

 

2.png

 

 

 

 

Anonymous
Not applicable

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")

@AlexChen

 

You are legend!!! Thanks a lot!!!

@AlexChen, thank you so much for the reply!!

 

 

Vvelarde
Community Champion
Community Champion

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

Anonymous
Not applicable

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).  

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.