March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi
I am trying to sum duration in my power bi report but can't get the time to format correctly
I have duration captured in the report in the following format
hh:mm
However in my report it sees 01:00 as 1am rather than 1 hour. I need to be able to sum the duration for each month in a matrix table as shown below
I have tried editing duration in power query editor to be 'duration' format but this doesnt seem to work either. I have also tried using another table to conver 01:00 to 1 (00:75 to 0.75 etc) but can't connect this as my data below is in a sub table from another data set. Essentially I dont mind if it counts in time or units, as long as I can see how much time something has taken.
Data | ||||
Month ID | Productfor | Duration | ||
1 | XYZ | 01:00 | ||
1 | XYZ | 02:00 | ||
3 | XYZ | 00:45 | ||
5 | XYZ | 04:30 | ||
5 | XYZ | 04:30 | ||
5 | XYZ | 04:30 | ||
Desired output | ||||
Month ID | ||||
1 | 2 | 3 | 4 | 5 |
03:00 | 0 | 0.45 | 0 | 13:30 |
Many thanks
Lea
Solved! Go to Solution.
Hi Lee
As you progress from Power BI novice to expert, it is vital to understand the different datatypes.
Time dataypes do what they they say on the tin. They only go upto 24:00hrs and are no good for summing duration.
The solution is to sum duration as an second integers (eg 70 secs) and then display it as hh:mm:ss text (eg 00:01:10)
The following example displays a deicmal 5.5 mins as 00:05:30 (5 mins and 30 seconds)
I am a Power BI volunteer. Please click the thumbs up for me going to the effort of trying to helping you. Then click solved if I fix your problem. One problem per ticket please. If you need to expand or change your problem then please raise a new ticket and click solved on this one so we get kudos. Many thanks.
Duration MM:SS =
-- Take decimal minutes as input eg 5.5 minutes
VAR DecimalMinutes = SUM(Telephone[Conversation duration])
-- Convert decimal minutes to seconds
VAR Duration = DecimalMinutes * 60
-- Calc the days, hours, minutes and seconds (add days if required)
VAR Hours = INT(DIVIDE(Duration,3600,0))
VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)
-- Format hours, minutes and seconds to two decimals
VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))
VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))
VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))
RETURN
-- if input is 0 return 00:00.
-- return hours:mins:seconds as text
-- handles any errors(unlikely)
-- if input is greater than 24hrs then the hours will be displayed
IFERROR(
IF (DecimalMinutes>0,
H & ":" & M & ":" & S,
"0:00"),
"Error")
Hi
Thanks for the super quick reply
So do I need to change the data input from 01:00 to 00:01:00
Then where would I put the formula you've sent as I'm working with a subtable?
Thanks, I think I have 'thumb's up'd' and accepted as super detailed reply, apologies I am still a little unclear
thanks
Hi lea_313
It is it vital you do some online training to understand the different datatypes. Also the difference between datatypes and formating.
2 minutes is 120 seconds but it is also 0.01333333333 an hour.
Your user may record telphone calls by minutes, seconds or hours.
But if you sum lots of phone calls you will soon exceed the 24:00 maximum a for time datatype.
So you need to be smart and be able to convert the time units to intergers or decimals to sum then.
Then use the solution I previously provide to display the sum at text.
01:00 to 00:01:00
Hi Lee
As you progress from Power BI novice to expert, it is vital to understand the different datatypes.
Time dataypes do what they they say on the tin. They only go upto 24:00hrs and are no good for summing duration.
The solution is to sum duration as an second integers (eg 70 secs) and then display it as hh:mm:ss text (eg 00:01:10)
The following example displays a deicmal 5.5 mins as 00:05:30 (5 mins and 30 seconds)
I am a Power BI volunteer. Please click the thumbs up for me going to the effort of trying to helping you. Then click solved if I fix your problem. One problem per ticket please. If you need to expand or change your problem then please raise a new ticket and click solved on this one so we get kudos. Many thanks.
Duration MM:SS =
-- Take decimal minutes as input eg 5.5 minutes
VAR DecimalMinutes = SUM(Telephone[Conversation duration])
-- Convert decimal minutes to seconds
VAR Duration = DecimalMinutes * 60
-- Calc the days, hours, minutes and seconds (add days if required)
VAR Hours = INT(DIVIDE(Duration,3600,0))
VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)
-- Format hours, minutes and seconds to two decimals
VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))
VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))
VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))
RETURN
-- if input is 0 return 00:00.
-- return hours:mins:seconds as text
-- handles any errors(unlikely)
-- if input is greater than 24hrs then the hours will be displayed
IFERROR(
IF (DecimalMinutes>0,
H & ":" & M & ":" & S,
"0:00"),
"Error")
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
133 | |
90 | |
88 | |
64 | |
58 |
User | Count |
---|---|
202 | |
137 | |
106 | |
70 | |
68 |