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
thampton
Helper III
Helper III

Sum of time not going over 24 hours

I have seconds in a column and converting this to a time HH:mm:ss with the function below. I have noticed this worked perfectly except it never goes over 24 hours (When looking at the data, it definitely should). Also, i am not able to add these together. Is there a way i can change the function below to achieve this?

 

Got this off youtube at some point, it does work great with the conversion!

 

Final Duration = // Duration formatting
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = [Total Seconds]
// There are 3,600 seconds in an hour
VAR Hours =
INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours
VAR Seconds =
ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
// Seconds with leading zeros
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
CONCATENATE (
H,
CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
)

 

 

1 ACCEPTED SOLUTION

@thampton

 

well like i said before, I tried it with 90,064 seconds (25 hours, 1 min and  4 seconds) and I get a correct

25:01:04

so it seems to work fine

View solution in original post

11 REPLIES 11
james_pbi
Frequent Visitor

Ive been trying to replicate this, but only with 'half' success.

For variable H, I could only get this to work if I swapped CONCATENATE ( "0", Hours ), for CONCATENATE ( 0, Hours ) and CONCATENATE ( "", Hours ) for Hours. Similarly for M and S.

Without this change, all I have output is blanks.

With the change, I can return H, or M, or S, on there own, and the result seems ok.

 
Then, when trying to return the final measure, all I have output is ::
Not H:M:S
 
Any assistance on this would be greatly appreciate - I've spent half a day trying to get something to work, that I'd have hoped would have been a standard format...

So, more hours of checking.

The formula works - but only when that table is used in isolation. When I create a relationship to another table, :: are displayed.

AlB
Super User
Super User

Hi @thampton

 

what do you mean it doesn't go over 24 hours? I just tried 90,064 seconds (25 hours, 1 min and  4 seconds) and I get a correct

25:01:04

For example, I have the rows as shown below and the total does not add up. I am not sure where it is getting the total. 

 

Row                    Time

1                          22:37:42

2                          19:59:09

3                          19:16:21

4                          15:40:28

5                          09:16:36

6                          06:16:31

7                          02:55:14

TOTAL                  00:02:01

 

@thampton

 

I see. That's a different story. But the problem then lies then most likely with the result [Total Seconds] is yielding and not with [Final Duration]

What you're showing is a matrix visual?  What goes into the column Time?

I am adding two measures together (each summing the final duration column on their respective table) to create the value in Time column. Its funny, as it works when the total is under 24 hours. 

@thampton

Can you share the pbix? 

Or can you try to show in Time the number of seconds resulting from the addition of those two measures, without the conversion, to see if the value to be converted is actually above 86400 (24 hours)?

I think i see the issue, i am not account for a day in my function. I tried 100485 seconds and it showed 03:54:45. This would be correct as it is 27 hours. 

 

Is there a way to show only hours instead of adding days into function. I would ideally like to have 27:54:45.

@thampton

I don't understand what you mean. What function are you talking about? The measure [Final duration]? 

The DAX above where i am doing the conversion. I do not account for days in that which is why when adding togeher, it rolls over at 24 hours (this is what im thinking). 

 

@thampton

 

well like i said before, I tried it with 90,064 seconds (25 hours, 1 min and  4 seconds) and I get a correct

25:01:04

so it seems to work fine

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.