Sum of Duration

[lea_313]

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

[lea_313]

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

[speedramps]

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

[speedramps · Accepted 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")