Number of Hours to Days/Hours/Minutes FORMAT don't convert properly, HELP!

[delphinecarnet]

I calculated the hours to days in Excel vs PowerBi and do not get the same results.

I'm averaging the number of hours + format it in a days/ hours/ minutes format

THANKS!

Here is the 

Avg Time = 

VAR 
total_hours = 

    SUMX( 

        Sheet2, 

        HOUR(Sheet2[Responses time]) +
MINUTE(Sheet2[Responses time])/60

)

VAR 
Daystotal = Total_hours/24

VAR 
DaysInt = TRUNC(DaysTotal)

VAR 
Hourstotal = (DaysTotal - 
DaysInt)*24

VAR 
HoursInt = TRUNC(HoursTotal)

VAR 
MinutesTotal = (HoursTotal - 
HoursInt)*60

VAR 
MinutesInt = TRUNC(MinutesTotal)

VAR 
SecondsTotal = ROUND(MinutesTotal -
MinutesInt*60,0)

RETURN

DaysInt&"d, "&FORMAT(HoursInt,"00")&"h, "&FORMAT(MinutesInt,"00")&"m, "

[v-yangliu-msft · Accepted solution]

Hi  @delphinecarnet ,

Here are the steps you can follow：

1. Create measure.

Avg Time =
VAR OverTimeHours = [Average of Respones time]
VAR Days = INT ( OverTimeHours / 24 )
VAR Hours = INT ( OverTimeHours - Days * 24 )
VAR Minutes = ROUND ( MOD ( OverTimeHours, 1 )* 60 , 0)
RETURN
Days & " Days " & Hours & " Hours " & Minutes & " Minutes"

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[FreemanZ]

how does your data in column [Responses time] look like? 

[delphinecarnet]

Customer No	Responses time
14447905	28
14447905	3
14447905	0
14447905	24
14447905	109
14447905	165
14447905	85
14447905	4
14447905	909
14447905	353
14447905	3
14447905	170
14447905	190
14447905	454
14447905	3
14447905	568
14447905	302
14447905	865
14447905	82
14447905	31
14447905	25
14447905	45
14447905	24
14447902	93
14447902	0
14447902	15
14447902	147
14447902	0
14447902	28
14447902	68
14447902	2
14447902	0
14447902	0
14447902	168
14447902	42
14447902	162
14447902	155
14447902	139
14447902	354
14447902	0
14447902	23
14447902	91
14447902	70
14447902	112
14447902	931
14447902	72
14447902	135
14447902	43
14447902	2
14447902	19
14447902	1,053
14447902	19
14447902	3
14447902	0
14447902	233
14447902	0
14447902	0
14447902	27
14447902	1
14447902	0
14447902	105
14447902	43
14447902	328
14447902	161
14447902	2
14447902	0
14447902	48
14499417	0
14499417	25
14499417	927
14499417	2,163
14499417	3
14499417	119
14499417	119
14499417	0
14499417	1,772
14499417	66
14499417	25
14499417	43
14499417	46
14499417	192
14499417	0
14499417	0
14499417	0
14499417	141
14499417	0

[FreemanZ]

it seems the response time is in minute, or?

try change the SUMX part to

SUMX(Table2, Table2[Response Time]/60)

?

[delphinecarnet]

You actually gave me a big hint on the solution.

I have found this DAX on a blog, but it was meant for the a number of days rather than a number of hours.

Here is the DAX that works:

Avg Time =

VAR
Total_hours = AVERAGE(Database[OSRT (Customer View)(Hrs)])

VAR
Daystotal = Total_hours/24

VAR
DaysInt = TRUNC(Daystotal)

VAR
Hourstotal = (Total_hours -
(DaysInt*24))

VAR
HoursInt = TRUNC(Hourstotal)

VAR
Minutestotal = (Hourstotal -
HoursInt)*60

VAR
MinutesInt = TRUNC(Minutestotal)

RETURN

DaysInt&"days "&FORMAT(HoursInt,"00")&"hours "&FORMAT(MinutesInt,"00")&"minutes"