topic Re: Incorrect Total time in hh:mm:ss Format in DAX Commands and Tips

Incorrect Total time in hh:mm:ss Format

Akhil008 — Fri, 14 Jul 2023 05:00:14 GMT

I'm trying to calculate the sum of time using the below formula but unable to get the desired result because the dax is not able to convert 00:00:00 to 12:00:00. I even tried converting the column to the 12hr format but still unable to achieve the result.

Formula:

var TotalTime=SumX(

data,

hours(data[logintime]) + Divide(minute(data[logintime]),60) +Divide(second(data[logintime]),3600) )

var days=Divide(TotalTime,24)

var Hours=(days)*24

var HoursInt=Trunc(Hours)

var Minutes=(Hours - HoursInt)*60

var MinutesInt=Trunc(Minutes)

var Seconds=Round ((Minutes-MinutesInt)*60,0)

Return

Format(HoursInt,"00")&"h,"& Format(MinutesInt, "00")&"m,"&Format(Seconds, "00")&"s"

Re: Incorrect Total time in hh:mm:ss Format

tamerj1 — Fri, 14 Jul 2023 06:59:45 GMT

Hi Akhil008
Why do you want to sum the time? What does that mean?

Re: Incorrect Total time in hh:mm:ss Format

Akhil008 — Fri, 14 Jul 2023 16:53:42 GMT

Hi, I want to calculate the total login time for the agents. I have a column that have login time in seconds and I've transformed the column in hh:mm:ss and now I want the sum of hours ,minutes and seconds in the format hh:mm:ss

Re: Incorrect Total time in hh:mm:ss Format

tamerj1 — Fri, 14 Jul 2023 17:34:19 GMT

Akhil008
Please try

Login = VAR TotalSeconds = 263117 VAR Seconds = MOD ( TotalSeconds, 60 ) VAR TotalMinutes = QUOTIENT ( TotalSeconds, 60 ) VAR Minutes = MOD ( TotalMinutes, 60 ) VAR TotalHours = QUOTIENT ( TotalMinutes, 60 ) VAR Hours = MOD ( TotalHours, 24 ) VAR Days = QUOTIENT ( TotalHours, 24 ) -- just in case required RETURN FORMAT ( Hours, "00" ) & "h," & FORMAT ( Minutes, "00" ) & "m," & FORMAT ( Seconds, "00" ) & "s"

Re: Incorrect Total time in hh:mm:ss Format

tamerj1 — Fri, 14 Jul 2023 18:34:13 GMT

Akhil008

Sorry did not clarify the solution. The integer in the TotalSeconds variable shall be simply replaced with sum of the time in seconds column.