How do I calculate time in Power BI?

[Anonymous]

I have been trying to create a measure that will accruately show me the time per person in Power BI related to talk time. So calcualting how much each person has for talk time but the formulas I am trying keep giving me the wrong answer. I am trying to caluclate the total talk time and then add each "Agent Name"(column with all agents) and show what each agents talk time is.

I have the column (BILL TIME)in power query already set to duration which shows the correct duration .

I have tried running these two formulas

Total Talk Time = SUM(data[BILL TIME])

String Duration in Hours and Minutes =

var vMinues=[Total Talk Time]

var vHours=int( vMinues/60)

var vRemainingMinutes=MOD(vMinues, 60)

return

vHours&" Hours & "& vRemainingMinutes& " Minutes"
--------------------------------------------------------------------

TimeCosts =

VAR total =

SUM ( data[BILL TIME] )

VAR hours =

INT ( total / 3600 )

VAR minutes =

INT ( MOD ( total, 3600 ) / 60 )

VAR seconds =

MOD ( MOD ( total, 3600 ), 60 )

RETURN

hours & " hours "

& minutes

& " minutes "

& seconds

& " seconds"

----------------------------------------------

talk_time = VAR Elapsed_Time = SELECTEDVALUE(data[BILL TIME ])

VAR days = INT(Elapsed_Time)

VAR _hrs = (Elapsed_Time - days) = 24

VAR hrs = INT(_hrs)

VAR mins = ROUND((_hrs - hrs) * 60,0)

Return

days & " d " & FORMAT(hrs,"00") & " h " & FORMAT(mins,"00") & " m "

any other way I can do this?

[mahoneypat · Accepted solution]

Another approach you can try (if your total duration is <24 hrs) is an expression like this.

Total CallTime =
FORMAT ( CONVERT ( SUM ( CallTime[CallTime] ), DATETIME ), "hh:mm:ss" )

If the total is >24 hrs, you can use this expression.

Total CallTime =
VAR dt =
    CONVERT ( SUM ( CallTime[CallTime] ), DATETIME )
VAR days =
    FORMAT ( DATEDIFF ( DATE ( 1899, 12, 30 ), dt, DAY ), "00:" )
VAR hhmmss =
    FORMAT ( dt, "hh:mm:ss" )
RETURN
    days & hhmmss

Pat

[Anonymous]

Thank you! exactly what I am looking for!

[Anonymous]

Sorry! I see your values are already decimal...in other words, already divided by 86400. This greatly simplifies the whole thing:

Add a total time measure:

TotalTime =  SUM(data[BILL TIME])

Then 4 measures each for day part, hour part, minute part, and second part:

IntDay = VAR DayFrac = Divide(Day([Total Time], [Divisor]) VAR GetDay = Day(DayFrac) RETURN GetDay

IntHour = VAR DayFrac = Divide([Total Time], [Divisor]) VAR GetHour = Hour(DayFrac) RETURN GetHour

IntMin = VAR DayFrac = Divide([Total Time], [Divisor]) VAR GetMin = MINUTE(DayFrac) RETURN GetMin

IntSec = VAR DayFrac = Divide([Total Time], [Divisor]) VAR GetSec = SECOND(DayFrac) RETURN GetSec

Then your actual Total Duration measure will be:

Total Duration = TIME([IntHour], [IntMin], [IntSec])

TotalTime =  SUM(data[BILL TIME])

Then 4 measures each for day part, hour part, minute part, and second part:

IntHour = VAR GetHour = Hour([Total Time]) RETURN GetHour

IntMin = VAR GetMin = MINUTE([Total Time]) RETURN GetMin

IntSec =VAR GetSec = SECOND([Total Time]) RETURN GetSec

Then your actual Total Duration measure will be:

Total Duration = TIME([IntHour], [IntMin], [IntSec])

[Anonymous]

I do it as follows.  I make a Divisor Measure:

Divisor = 86400

Add a total time measure:

TotalTime =  SUM(data[BILL TIME])

Then 4 measures each for day part, hour part, minute part, and second part:

IntDay = VAR DayFrac = Divide([Total Time], [Divisor]) VAR GetDay = Day(DayFrac) RETURN GetDay

IntHour = VAR DayFrac = Divide([Total Time], [Divisor]) VAR GetHour = Hour(DayFrac) RETURN GetHour

IntMin = VAR DayFrac = Divide([Total Time], [Divisor]) VAR GetMin = MINUTE(DayFrac) RETURN GetMin

IntSec = VAR DayFrac = Divide([Total Time], [Divisor]) VAR GetSec = SECOND(DayFrac) RETURN GetSec

Then your actual Total Duration measure will be:

Total Duration = TIME([IntHour], [IntMin], [IntSec])

Now you have Total Duration in h:mm:ss format.

--Nate

[Anonymous]

Hey Nate, tried out your steps and I dont beleive it is working. this is the result I am getting for one person when it shuld my multiple hours.

[Anonymous]

thanks nate, tried out those steps but dont believe its working unless I am doing something wrong 

[Greg_Deckler]

@Anonymous Try Chelsie Eiden's Duration: Chelsie Eiden's Duration - Microsoft Power BI Community

[Anonymous]

anyway that breaks it up into hours minutes seconds to be able to add all together?