issue timing format mm:ss.00 form

[davorgom]

Hi, 

I have a problem tryng to get timing runners in power bi in mm:ss.00 format. Someone knows how can I do it?

[v-yuta-msft]

Hi davorgom,

Click Modeling-> Data Time->Format(hh:mm:ss tt)

Regards,

Jimmy Tao

[davorgom]

HI, how are you? Thanks to your answer.

I have to say that it doesn´t work. If I put that format, the data appears in a "date mode" (12:00:00 am), not the athletes records with hundredths of a second.

I think that microsft has to improve this, and we have to collect lot of votes 😞

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15906970-milliseconds-as-format-o...

Do you have another idea?

Thank u. 

[Greg_Deckler · Accepted solution]

Give this a whirl. I'm going to post it to the Quick Measures Gallery. It is based on the blog post that I replied with earlier.

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 ( "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 ) ) ) ) ) )

I believe it is correct but vet it out please. Also attaching PBIX.

[Greg_Deckler]

I posted the solution to the Quick Measure Gallery here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Milliseconds-Duration/m-p/406698#M141

I did make one tweak to the leading zeros for milliseconds.

[Greg_Deckler]

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486