How can I create a DAX measure to add time in hh:mm format?

[HamidBee]

I am working with the following data:

Letter	Duration
A	04:40
A	05:20
B	01:20
B	05:43
C	06:34
C	02:45
D	09:21
D	10:12
E	04:39
E	05:30

What I would like to be able to do is to find the total duration for each letter but to keep it in the hh:mm format. I'd like the hh to be able to exceed 24. I would like to plot this in a matrix. However when I try to do this it appears that I cannot. Here is a link to the .pbix file:

https://www.mediafire.com/file/8acsm9irdvoxtcv/Adding_Times.pbix/file

Thanks in advance.

[ryan_mayu · Accepted solution]

@HamidBee 

here is a workaround for you.

1. create a min column

duration min = hour('Table'[Duration])*60+MINUTE('Table'[Duration])

2. create a measure

Measure =

VAR _sum=sum('Table'[duration min])

VAR _hour=right("0"&ROUNDDOWN(_sum/60,0),2)

VAR _min=right("0"&mod(_sum,60),2)

return _hour&":"&_min

pls see the attachment below

[vojtechsima · Accepted solution]

Hello, @HamidBee

Total Duration = 

var Hours = CALCULATE( SUMX('Table', HOUR('Table'[Duration])), ALLEXCEPT('Table','Table'[Letter]))
var Minutes = CALCULATE( SUMX('Table', MINUTE('Table'[Duration])), ALLEXCEPT('Table','Table'[Letter]))

var MinutesForHours = DIVIDE ( Minutes , 60 )
var SingleMinutes = MOD(Minutes, 60)
var HoursWithMinutes = TRUNC( Hours + MinutesForHours)

var Result = FORMAT(HoursWithMinutes, "00") & ":" & FORMAT(SingleMinutes, "00")


return Result

[vojtechsima · Accepted solution]

Hello, @HamidBee

Total Duration = 

var Hours = CALCULATE( SUMX('Table', HOUR('Table'[Duration])), ALLEXCEPT('Table','Table'[Letter]))
var Minutes = CALCULATE( SUMX('Table', MINUTE('Table'[Duration])), ALLEXCEPT('Table','Table'[Letter]))

var MinutesForHours = DIVIDE ( Minutes , 60 )
var SingleMinutes = MOD(Minutes, 60)
var HoursWithMinutes = TRUNC( Hours + MinutesForHours)

var Result = FORMAT(HoursWithMinutes, "00") & ":" & FORMAT(SingleMinutes, "00")


return Result

[HamidBee]

Thanks for this method it also works perfectly.

[saravanan_p]

create a column in table :

column = 

(duration_test[H] *60) + duration_test[m]

ANd write measure as 

duration_m =

var d = sum(duration_test[Column])

var h =right("0" & rounddown(d/60,0),2)

var m=right("0" & MOD(d,60),2)

return

h&":"&m
@HamidBee  The reply from @ryan_mayu  is also the same. Both works and request you to mark both as solutions.

Thank you Guys, Good Day

[saravanan_p]

@ryan_mayuand @HamidBee Dont forget to give thumps up

[ryan_mayu · Accepted solution]

@HamidBee 

here is a workaround for you.

1. create a min column

duration min = hour('Table'[Duration])*60+MINUTE('Table'[Duration])

2. create a measure

Measure =

VAR _sum=sum('Table'[duration min])

VAR _hour=right("0"&ROUNDDOWN(_sum/60,0),2)

VAR _min=right("0"&mod(_sum,60),2)

return _hour&":"&_min

pls see the attachment below

[HamidBee]

I would like to ask what the following means:

VAR _hour=right("0"&ROUNDDOWN(_sum/60,0),2)

I understand it to mean that you are converting the minutes into hours and then removing any decimal places by using '2'. I'm familiar with what RIGHT does but I don't understand why "0" was used and why "&" was used.

VAR _min=right("0"&mod(_sum,60),2)

I believe you are taking the sum here and dividing it by 60 and keeping only the remainder. Again I'm not too sure what the RIGHT function and '2' are for., 

If you can please clarify these points that would be greatly appreciated. 

Thank you.

[ryan_mayu · Accepted solution]

@HamidBee 

That means if the hour is one digit, then add 0 before it. 

if the hour is two digits, then add 0 before it and get the 2 digits from the right side.

However, here is a problem. If the hour is larger than 99, this solution will not work.

you may change to this.

Measure =

VAR _sum=sum('Table'[duration min])

VAR _hour=if(ROUNDDOWN(_sum/60,0)<10,"0"&ROUNDDOWN(_sum/60,0),ROUNDDOWN(_sum/60,0)

VAR _min=if(mod(_sum,60)<10,"0"&mod(_sum,60),mod(_sum,60))

return _hour&":"&_min

[saravanan_p]

@ryan_mayu Man O Man, super fast. Same findings. Cheers.