Convert seconds to HH:MM:SS then SUM

[GlitchedDuck]

Hi All,

Having loads of trouble trying to get this to work.

I have a column that has the duration a session was open in a job, i want to convert that the Time HH:MM:SS and be able to sum it by month.

Heres some of the data.

Thanks

mod_name	end_date_and_time	batch_id	sess_duration_sec
Validation Activity	11/02/2022 15:05	955b4fa2-1b4e-4a9b-bd88-ae38011319f8	30
Validation Activity	11/02/2022 15:05	aa5bc5b6-310c-48e6-9c88-ae3801131c54	30
Validation Activity	11/02/2022 15:26	40106b1d-5dd4-469b-98c3-ae38011338f2	30
Validation Activity	11/02/2022 15:26	6621e4a0-2795-4d85-aece-ae3801134a8b	30
Validation Activity	11/02/2022 15:26	00dd25ca-13c9-45bd-8d49-ae3801133f04	30
Validation Activity	11/02/2022 15:26	88d607ab-912e-491f-8e99-ae3801135335	30
Validation Activity	11/02/2022 15:26	86bc6ff3-cd96-4e31-9b6b-ae380113569b	30

[ryan_mayu]

@GlitchedDuck 

pls try this

Column = 
var _hour=int('Table'[sess_duration_sec]/3600)
var _min=int(('Table'[sess_duration_sec]-_hour*3600)/60)
var _sec='Table'[sess_duration_sec]-_hour*3600-_min*60
return time(_hour,_min,_sec)

you can use similar method  to convert sum value.

[GlitchedDuck]

Still won't allow me to sum. I want to see a total time of 03:30 based on those numbers

[ryan_mayu · Accepted solution]

@GlitchedDuck 

pls see the attachment below

[GlitchedDuck]

Worked. Thank you!

[ryan_mayu]

you are welcome

[GlitchedDuck]

Quick question. When i sum the time and it goes over 24h the sum resets. so for example the total sum is 29hours and 56mins. It shows 05:56:30 as the total and not 29:56:30. Can this be done?

[ryan_mayu]

I didn't tried that, but I think the time type maybe not allow us to set the hour more than 24.

If you want the hour to be more than 24, then you can set this as a text type. Then this measure looks like time, but it's a text and can not do the calculation.

Measure2 = 
VAR _SUM=sum('Table'[sess_duration_sec])
var _hour=int(_SUM/3600)
var _min=int((_SUM-_hour*3600)/60)
var _sec=_SUM-_hour*3600-_min*60
var _hour2=if(_hour=0,"00",if(_hour<10,"0"&_hour,_hour))
VAR _min2=if(_min=0,"00",if(_min<10,"0"&_min,_min))
var _sec2=if(_sec=0,"00",if(_sec<10,"0"&_sec,_sec))
return _hour2&":"&_min2&":"&_sec2

pls see the attachment below

[ryan_mayu]

@GlitchedDuck 

pls try this

Column = 
var _hour=int('Table'[sess_duration_sec]/3600)
var _min=int(('Table'[sess_duration_sec]-_hour*3600)/60)
var _sec='Table'[sess_duration_sec]-_hour*3600-_min*60
return time(_hour,_min,_sec)

you can use similar method  to convert sum value.

[amitchandak]

@GlitchedDuck , you have convert to second to minutes and add that

New column = hour([time])*3600 + Minute([Time])*60 + second([time])

Convert the sum for display

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...

[GlitchedDuck]

Just showing up as 0 when i do this