cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thampton Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: Sum of time not going over 24 hours

@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

9 REPLIES 9
Super User
Super User

Re: Sum of time not going over 24 hours

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

thampton Regular Visitor
Regular Visitor

Re: Sum of time not going over 24 hours

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

 

Super User
Super User

Re: Sum of time not going over 24 hours

@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?

thampton Regular Visitor
Regular Visitor

Re: Sum of time not going over 24 hours

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. 

Super User
Super User

Re: Sum of time not going over 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)?

thampton Regular Visitor
Regular Visitor

Re: Sum of time not going over 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.

Super User
Super User

Re: Sum of time not going over 24 hours

@thampton

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

thampton Regular Visitor
Regular Visitor

Re: Sum of time not going over 24 hours

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

 

Super User
Super User

Re: Sum of time not going over 24 hours

@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