Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How do I calculate time in Power BI?

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?



General Comment
Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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
     

8 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you! exactly what I am looking for!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    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])