Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
Working on calculating a time to resolution metric for our help desk by subtracting the "Completed By" system date and time from the "Created On" system date and time.
Time to Resolution = Completed By - Created On
Time to Resolution is a custom format in Excel (dd hh:mm)
In Excel, I can sum, average, and count the Time to Resolution metric.
When imported into Power BI Desktop, it changes the format to:
I've tried different change types and transform options, but am having trouble. I'm fairly new to Power BI and am unsure if I can create a calculated column with a custom date format that can be used to sum and average, just like in Excel.
Any help would be very much appreciated.
Solved! Go to Solution.
Hi @tykurzy
When converting to DateTime, you should use Change Type -> Using Locale
If your dates are in dd/mm/yyyy format then use a locale that matches like English (Australia) or English (United Kingdom)
Regards
Phil
Proud to be a Super User!
Hi @tykurzy
In this file I've imported your Excel file then converted the Created On and Completed By columns to DateTime Using Locale.
I've then subtracted one from the other in PQ and changed that resultant column, called Time to Resolution, to Duration type.
Once loaded into the data model, the Time to Resolution will be displayed as a decimal, because that's how it is stored. DAX doesn't have ther Duration data type.
In order to display these durations in a human friendly format, I've used some code to do that formatting. I've created a measure called Resolution Time
Resolution Time =
VAR Elapsed_Time = SELECTEDVALUE('Transform'[Time To Resolution])
VAR days = INT(Elapsed_Time)
VAR _hrs = (Elapsed_Time - days) * 24
VAR hrs = INT(_hrs)
VAR _mins = ROUNDUP((_hrs - hrs) * 60, 2)
VAR mins = INT(_mins)
VAR seconds = INT((_mins - mins) * 60)
RETURN
//Use FORMAT to FORMAT the string to use 2 chars e.g. 03 rather than just 3
days & " d " & FORMAT(hrs,"00") & " h " & FORMAT(mins,"00") & " m " & FORMAT(seconds,"00") & " s"
With this added to a table visual, your resolution times are easier to understand
You can change the final format as desired. Some background on this measure and durations in DAX
Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI • My Online Training Hub
You can remove the Time to Resolution column from this table. I left it in just to illustrate what it looks like before formatting with my measure.
When you open the PBIX, you'll of course need to change the Source step to point to your Excel file.
Regards
Phil
Proud to be a Super User!
Hi @tykurzy
Use this measure
Avg Resolution Time =
VAR Elapsed_Time = CALCULATE(AVERAGEX('DataTable','DataTable'[Time To Resolution]), FILTER(ALL('DataTable'), 'DataTable'[Owner First Name] = SELECTEDVALUE('DataTable'[Owner First Name])))
VAR days = INT(Elapsed_Time)
VAR _hrs = (Elapsed_Time - days) * 24
VAR hrs = INT(_hrs)
VAR _mins = ROUNDUP((_hrs - hrs) * 60, 2)
VAR mins = INT(_mins)
VAR seconds = INT((_mins - mins) * 60)
RETURN
//Use FORMAT to FORMAT the string to use 2 chars e.g. 03 rather than just 3
days & " d " & FORMAT(hrs,"00") & " h " & FORMAT(mins,"00") & " m " & FORMAT(seconds,"00") & " s"
Regards
Phil
Proud to be a Super User!
Thanks @PhilipTreacy! Looks like I need to start learning DAX.
@Daniel_Fdrvc, check out Phil's solution. Worked well for me.
Thanks for your help, @PhilipTreacy
I'm almost there.
My next challenge is in building my visualization...
As you can see above, I am averaging the Response Time by help desk employee. This is fantastic but 4.32 is not easily understood. How do I apply Phil's human-friendly formatting d / m / h / s to this simple table visualization?
Appreciate the help!
Tyler
Hi @tykurzy
Use this measure
Avg Resolution Time =
VAR Elapsed_Time = CALCULATE(AVERAGEX('DataTable','DataTable'[Time To Resolution]), FILTER(ALL('DataTable'), 'DataTable'[Owner First Name] = SELECTEDVALUE('DataTable'[Owner First Name])))
VAR days = INT(Elapsed_Time)
VAR _hrs = (Elapsed_Time - days) * 24
VAR hrs = INT(_hrs)
VAR _mins = ROUNDUP((_hrs - hrs) * 60, 2)
VAR mins = INT(_mins)
VAR seconds = INT((_mins - mins) * 60)
RETURN
//Use FORMAT to FORMAT the string to use 2 chars e.g. 03 rather than just 3
days & " d " & FORMAT(hrs,"00") & " h " & FORMAT(mins,"00") & " m " & FORMAT(seconds,"00") & " s"
Regards
Phil
Proud to be a Super User!
Hi @tykurzy
In this file I've imported your Excel file then converted the Created On and Completed By columns to DateTime Using Locale.
I've then subtracted one from the other in PQ and changed that resultant column, called Time to Resolution, to Duration type.
Once loaded into the data model, the Time to Resolution will be displayed as a decimal, because that's how it is stored. DAX doesn't have ther Duration data type.
In order to display these durations in a human friendly format, I've used some code to do that formatting. I've created a measure called Resolution Time
Resolution Time =
VAR Elapsed_Time = SELECTEDVALUE('Transform'[Time To Resolution])
VAR days = INT(Elapsed_Time)
VAR _hrs = (Elapsed_Time - days) * 24
VAR hrs = INT(_hrs)
VAR _mins = ROUNDUP((_hrs - hrs) * 60, 2)
VAR mins = INT(_mins)
VAR seconds = INT((_mins - mins) * 60)
RETURN
//Use FORMAT to FORMAT the string to use 2 chars e.g. 03 rather than just 3
days & " d " & FORMAT(hrs,"00") & " h " & FORMAT(mins,"00") & " m " & FORMAT(seconds,"00") & " s"
With this added to a table visual, your resolution times are easier to understand
You can change the final format as desired. Some background on this measure and durations in DAX
Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI • My Online Training Hub
You can remove the Time to Resolution column from this table. I left it in just to illustrate what it looks like before formatting with my measure.
When you open the PBIX, you'll of course need to change the Source step to point to your Excel file.
Regards
Phil
Proud to be a Super User!
Hi @tykurzy
When converting to DateTime, you should use Change Type -> Using Locale
If your dates are in dd/mm/yyyy format then use a locale that matches like English (Australia) or English (United Kingdom)
Regards
Phil
Proud to be a Super User!
Hi @tykurzy
If you subtract one DateTime from another you should end up with a duration in Power Query. This is a decimal number of days that is displayed in the format 1.12:30:00 meaning 1 day, 12 hours, 30 minutes, zero seconds.
You should change your Created On and Completed By columns to the DateTime type before doing the subtraction.
Regards
Phil
Proud to be a Super User!
Thanks, Phil. Trying to implement your solution but getting error messages when trying to change my date columns from text to Date/Time.
I suspect Power Query thinks my date field is mm/dd/yyyy; whereas the system date format I'm pulling in is dd/mm/yyyy.
Anything with a value over 12 in the "dd" part of the system date receives an error message.
Here is the error message:
DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
14/11/2018 10:53:04
Thanks for your help,
Tyler
Can you upload here that excel file with some dates? Maybe this will make it easier to replicate your problem and find a solution faster.
I'm new in PBI too, but founded some, maybe not user friendly solution to convert dates when they in format mm/dd/yyyy via new calculated column:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
151 | |
103 | |
102 | |
87 | |
63 |