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.
I have a column that is the raw out put of how long it has taken to respond to a ticket.
It is in the format dd/mm/yyyy hh:mm:ss
However the date starts at 01/01/4000 00:00:00. Screenshot below:
I need to covert it to a total durtaion i.e. 48:53:20 (HH:MM:SS) so that i can take an average of these values in another measure.
Any ideas how to do this? i have tried datediff but that only returns it ina single format of HOURS or Minutes etc...
Solved! Go to Solution.
Hi @Rince91
power bi desktop isn't able to format time beyond 23:59:59. In Excel you can use square brackets like this [hh]:mm:ss. Use a calculated column with the following formula:
Total Time =
VAR _Difference = 'Table'[End] - 'Table'[Start]
VAR _Days = INT(_Difference)
VAR _Hours = HOUR(_Difference)
VAR _Minutes = MINUTE(_Difference)
VAR _Seconds = SECOND(_Difference)
VAR _DaysToHours = _Days * 24
VAR _TotalHours = _DaysToHours + _Hours
RETURN
FORMAT(_TotalHours, "00") & ":" & FORMAT(_Minutes, "00") & ":" & FORMAT(_Seconds,"00")
Regards FrankAT
Hi @Rince91 ,
You can check this blog.
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @Rince91
power bi desktop isn't able to format time beyond 23:59:59. In Excel you can use square brackets like this [hh]:mm:ss. Use a calculated column with the following formula:
Total Time =
VAR _Difference = 'Table'[End] - 'Table'[Start]
VAR _Days = INT(_Difference)
VAR _Hours = HOUR(_Difference)
VAR _Minutes = MINUTE(_Difference)
VAR _Seconds = SECOND(_Difference)
VAR _DaysToHours = _Days * 24
VAR _TotalHours = _DaysToHours + _Hours
RETURN
FORMAT(_TotalHours, "00") & ":" & FORMAT(_Minutes, "00") & ":" & FORMAT(_Seconds,"00")
Regards FrankAT
hi,
i tried to do that also. after i calaulated this, i try to calculate average but it dose not work becuse its string. do you have maybe solution for this?
Sorry to try and clarify:
If a repsonse to a ticket is 1day 4 hours and 38 minutes 20 seconds the format of that data in the database i have access to is:
02/01/4000 04:38:20
I need this date time in the format of:
28:38:20
So converting the day to 24hours then adding it to the other 4hours 38 minutes 20 seconds, to give a total durtaion in a time format.
Once its in that format i can then make a measure which takes the average of those reposne times.
Hi @Rince91
You are going in right direction, you can create 3 calculated columns for hours, min and sec and then concate them or you can do the same in query editor, below is example to get time in hours.
TimeIn Hours = Duration.TotalHours(Duration.From ( [END_DATE] - [START_DATE])).
3rd approach is what Amit has shared you can refer to that on Radacad.
@Rince91 , Not very clear to me
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |