Forum Discussion

davorgom's avatar
davorgom
Frequent Visitor
8 years ago
Solved

issue timing format mm:ss.00 form

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?
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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.