Forum Discussion

randomanalyst's avatar
randomanalyst
New Member
3 years ago
Solved

Median Response Time

Hi all, 

 

I am calculating median response times from a duration variable and I have created a new column to convert that into seconds as follows:

 

Response Sec = 

VAR AttendedDateTime = TIME(HOUR('Response Details'[AttendedTime]), MINUTE('Response Details'[Attended Time]), SECOND('Response Details'[Attended Time]))

VAR Hours = HOUR(AttendedDateTime)

VAR Minutes = MINUTE(AttendedDateTime)

VAR Seconds = SECOND(AttendedDateTime)

RETURN

    Hours * 3600 + Minutes * 60 + Seconds
 
I then created three measures for first, second, and third priority incidents which have different service time requirements. Calculating first and second priority provides accurate results, however for the third priority which has response times over 24 hours in some cases, the median response time shows as 5 hours and 40 mins, when it should be around 10 hours as verified on another system.
 
Is something wrong with the way I have created the response sec or median measures for the third priority?
 
Here is the median response time measure for third priorities:
 
Third priority median YTD = 

VAR MED_Response =

    DIVIDE(

        CALCULATE(

            MEDIAN('Response Details'[Response Sec]),

            DATESYTD('Response Details'[Created Date], "03/31"),

            'Response Details'[Grade] = "Third Priority"

        ),

        86400,

        BLANK()

    )

VAR days = INT(MED_Response)

VAR _hrs = (MED_Response - days) * 24

VAR hrs = INT(_hrs)

VAR _mins = ROUNDUP((_hrs - hrs) * 60, 2)

VAR mins = INT(_mins)

VAR secs = INT((_mins - mins) * 60)

RETURN

    //days & " d " &

    FORMAT(hrs, "00") & ":" & FORMAT(mins, "00") & ":" & FORMAT(secs, "00")
 
 
Need Help
  • randomanalyst's avatar
    randomanalyst
    3 years ago

    Hi Greg_Deckler , 

     

    I came up with a solution, apparently the [response sec] was the part causing an issue. I am not using a [start time] and [end time] to calculate the time taken, the [attended time] is a decimal time value in the database, so I needed to re-write the [response sec] to better reflect the data type. I have referenced it below in case others need this solution:

     

    Response Sec = 
    VAR DecimalValue = 'Response Details'[Attended Time]
    VAR DecimalSeconds = [Attended Time] * 86400 // Convert decimal to seconds (86400 seconds in a day)
    RETURN
    ROUND(DecimalSeconds, 0) // Round the result to the nearest whole second

     Thanks

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    randomanalyst Perhaps providing some sample data would help. Maybe you are running into an issue where the duration spans 2 days and that is causing the issue? For example, it starts on 8/13/2023 at 7PM and shows up at 5AM the next day?

    • randomanalyst's avatar
      randomanalyst
      New Member
      3 years ago

      Hi Greg_Deckler , 

       

      I came up with a solution, apparently the [response sec] was the part causing an issue. I am not using a [start time] and [end time] to calculate the time taken, the [attended time] is a decimal time value in the database, so I needed to re-write the [response sec] to better reflect the data type. I have referenced it below in case others need this solution:

       

      Response Sec = 
      VAR DecimalValue = 'Response Details'[Attended Time]
      VAR DecimalSeconds = [Attended Time] * 86400 // Convert decimal to seconds (86400 seconds in a day)
      RETURN
      ROUND(DecimalSeconds, 0) // Round the result to the nearest whole second

       Thanks