Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
pls see the attachment below
Proud to be a Super User!
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.
Proud to be a Super User!
Still won't allow me to sum. I want to see a total time of 03:30 based on those numbers
pls see the attachment below
Proud to be a Super User!
Worked. Thank you!
you are welcome
Proud to be a Super User!
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?
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
Proud to be a Super User!
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.
Proud to be a Super User!
@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...
Just showing up as 0 when i do this
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |