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,
I have a sql field that returns an elapsed time value in milliseconds (i.e. days, hours, minutes, seconds)
Is there a simple way to convert that into something meaningful?
I've managed to convert it to a datetime at source by doing this which I think worked:
dateadd(millisecond, myfield %1000, dateadd(second, myfield / 1000, '19700101'))
Example: 30871000 milliseconds gives me 01/01/1970 08:34:31
Manual calculation of that is:
30871000 / 1000 = 30,871 seconds / 60 = 514 minutes / 60 = 8.57527777 hours, so about eight and a half hours.
The problem with that though is that this is then an elapsed time since 1 Jan 1970. Simply doing a SUM on these won't give me a total elapsed time that I can tell?
Any thoughts on how best to tackle this greatly appreciated.
Many thanks,
D.
ASA =
VAR durationAverage = CALCULATE(DIVIDE([Total Wait Time],[Calls Offered To Queue]))
VAR millisecondsStr = MOD(durationAverage, 1000)
VAR hoursInt = INT(DIVIDE(durationAverage, 3600000.0))
VAR hoursStr = RIGHT("00" & IF(ISBLANK(hoursInt),"",hoursInt),3)
VAR minutesInt = INT(DIVIDE(MOD(hoursInt 3600000.0), 60000.0))
VAR minutesStr = RIGHT("00" & IF(ISBLANK(minutesInt),"",minutesInt),2)
VAR secondsInt = MOD(INT(DIVIDE(MOD(minutesInt 3600000.0), 1000.0)), 60)
VAR secondsStr = RIGHT("00" & IF(ISBLANK(secondsInt),"",secondsInt),2)
RETURN hoursStr & ":" & minutesStr & ":" & secondsStr
I came with the following measure to convert a decimal-column of milliseconds into "hh:mm:ss.mmmm":
duration avg = VAR durationAverage = INT(AVERAGE(MyTable[duration])) VAR millisecondsStr = MOD(durationAverage, 1000) VAR secondsInt = MOD(INT(DIVIDE(durationAverage, 1000.0)), 60) VAR secondsStr = REPT("0", 2 - LEN(secondsInt)) & secondsInt VAR minutesInt = INT(DIVIDE(durationAverage, 60000.0)) VAR minutesStr = REPT("0", 2 - LEN(minutesInt)) & minutesInt VAR hoursInt = INT(DIVIDE(durationAverage, 3600000.0)) RETURN hoursInt & ":" & minutesStr & ":" & secondsStr & "." & millisecondsStr
Change AVERAGE(MyTable[duration]) with your input value.
Not sure if this is what you are going for, but myself and @konstantinos wrote an article on something very similar:
http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
You can do this in M:
#duration(0, 0, 0, numberOfMilliseconds)
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |