topic Re: Milliseconds Duration in Quick Measures Gallery

Milliseconds Duration

Greg_Deckler — Tue, 26 Jun 2018 11:51:50 GMT

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

Re: Milliseconds Duration

davorgom — Mon, 30 Apr 2018 01:12:45 GMT

Hi Greg_Deckler

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.

Re: Milliseconds Duration

Greg_Deckler — Mon, 30 Apr 2018 12:07:04 GMT

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.

Re: Milliseconds Duration

davorgom — Mon, 30 Apr 2018 17:36:51 GMT

HI Greg_Deckler

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

Re: Milliseconds Duration

vissvess — Mon, 22 Jul 2019 10:59:11 GMT

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

Re: Milliseconds Duration

HJC-AC — Wed, 07 Sep 2022 09:05:52 GMT

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