Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
From a duration column (displayed in visuals as a decimal) I'd create a measure, this measure aggregate the duration values and display it as a readable column, here the measure code:
Timing =
VAR dur = AVERAGE('Fact Tempi'[Durata])
VAR hours = INT ( MOD ( dur * 24 , 24 ) )
VAR minutes = INT ( MOD ( dur * 24 * 60, 60 ) )
VAR seconds = INT ( MOD( dur * 24 * 60 * 60, 60))
VAR millis = INT ( MOD ( dur * 24 * 60 * 60 * 1000, 1000 ))
VAR hoursText =
IF (
LEN ( hours ) = 1,
CONCATENATE ( "0", hours ),
CONCATENATE ( "", hours )
)
VAR minutesText =
IF (
LEN ( minutes ) = 1,
CONCATENATE ( "0", minutes ),
CONCATENATE ( "", minutes )
)
VAR secondsText =
IF (
LEN ( seconds ) = 1,
CONCATENATE ( "0", seconds ),
CONCATENATE ( "", seconds )
)
VAR millisText =
IF (
LEN ( millis ) = 3,
CONCATENATE ( "", millis ),
IF (
LEN ( millis ) = 1,
CONCATENATE ( "00", millis ),
CONCATENATE ( "0", millis )
)
)
RETURN
hoursText & ":" & minutesText & ":" & secondsText & "." & millisText
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)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
55 | |
54 | |
37 | |
29 |
User | Count |
---|---|
77 | |
62 | |
45 | |
40 | |
40 |