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?

  • 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.

5 Replies

    • davorgom's avatar
      davorgom
      Frequent Visitor

      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-option-for-date-time

       

      Do you have another idea?

       

      Thank u. 

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion

        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.