cancel
Showing results for
Did you mean:

# Re: Milliseconds Duration

Frequent Visitor
1076 Views
Super User

## Milliseconds Duration

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"
//
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
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
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 ) ) ) ) ) )```

Proud to be a Datanaut!

Highlighted
Frequent Visitor

## Re: Milliseconds Duration

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.

Super User

## Re: Milliseconds Duration

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.

Proud to be a Datanaut!

Frequent Visitor

## Re: Milliseconds Duration

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