04-29-2018 09:07 AM - last edited 06-26-2018 04:51 AM
Based on the original blog post around Duration from 1/25/16 by @konstantinos and later blogged about here:
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 ) ) ) ) ) )
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 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.
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?
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.