Milliseconds Duration 07-22-2019 03:59 AM

[Greg_Deckler]

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

[HJC-AC]

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

[vissvess]

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

[davorgom]

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

[davorgom]

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. 

[Greg_Deckler]

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.