Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Based on the original blog post around Duration from 1/25/16 by @konstantinos and later blogged about here:
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Extends the formatting of Duration to include milliseconds.
Duration Milliseconds = // Duration formatting // * Based on @konstatinos and @Greg_Deckler blog post // https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 1/25/2016 // * Given a number of milliseconds, returns a format of "hh:mm:ss:000 format" // // We start with a duration in number of milliseconds VAR Duration = SUM(Milliseconds[Milliseconds]) // There are 3,600,000 milliseconds in an hour VAR Hours = INT ( Duration / 3600000) // There are 60,000 milliseconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600000 ),3600000 ) / 60000) // There are 1000 milliseconds in a second VAR Seconds = INT (MOD ( MOD( Duration - ( Hours * 3600000 ) - (Minutes * 60000),60000 ), 60000 ) / 1000) VAR Milli = ROUNDUP(MOD(MOD ( MOD( Duration - ( Hours * 3600000 ),3600000 ), 60000 ), 1000),0) // These intermediate variables ensure that we have leading zero's concatenated onto single digits // Hours with leading zeros VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) ) // Minutes with leading zeros VAR M = IF ( LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ) ) // Seconds with leading zeros VAR S = IF ( LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ) ) // MilliSeconds with leading zeros VAR MS = IF ( LEN ( Milli ) = 1, CONCATENATE ( "00", Milli ), IF ( LEN (Milli) = 2, CONCATENATE("0", Milli), CONCATENATE ( "", Milli ) ) ) // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" RETURN CONCATENATE (H,CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", CONCATENATE(S, CONCATENATE(":", MS ) ) ) ) ) )
eyJrIjoiMzgwMWM4YzQtNmFhNy00NGI0LThiNjItZTMzMzAyYWQ2YTkyIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
Building on this in a simpler format, if you have a timestamp in milliseconds and want to convert it to a datetime:ms, then please see below. This is also incorporating the work from here https://community.powerbi.com/t5/Desktop/Parse-timestamps-to-date/m-p/155179
UTCTime = VAR msTimestamp = 'table'[timestamp]
VAR UnixDays = msTimestamp/(60*60*24*1000)
VAR Milli = RIGHT(msTimestamp,3)
VAR DateTime = (DATEVALUE("1/1/1970") + UnixDays)
RETURN CONCATENATE (FORMAT(DateTime, "dd/mm/yyyy hh:MM:ss"), CONCATENATE( ":", Milli))
Dear @Greg_Deckler ,
The code does well..
I tried it with my case where I need to plot the duration against a category where the duration is in hh:mm:ss:000 format.
When the same is tried for execution, the Y axis is shown as fractional number.
Any help would be appreciated.
Thanks
Thanks to your help. Your comments are very helpful.
I wanna say you that the idea is getting well, but I have problems with milliseconds to minuts. I don´t know if Im wrong. As you can see at the files attached, in a 100m race, a runner made 12.40 sgs, and it appears 12:400, its ok. But when the result is in minutes, for example a 1500m race, where the runner has 33:29.71, power bi shows 5:32:971. The graphs appears wrong too. There is a possibiity to improve this?
On another hand, when I try to graph the places of the 100m record, it shows 12.4mil. There is a possibility to eliminate the text "mil"? Change it for min?
Thanks for your time. Its really important that there is persons who wanna teach anothers to use this app.
Best regards.
David
I really apreciatte your time and your help. You had sent me a little code but I don`t know how and where can I put this on te app. Can u say me how to do it?
Thanks.
You can use this formula in a calculated column or in a measure. The only thing that really should need to change is this line:
VAR Duration = SUM(Milliseconds[Milliseconds])
My table is called Milliseconds and my column is called Milliseconds as well.
I attached the PBIX so you can download it and see how it functions.