cancel
Showing results for 
Search instead for 
Did you mean: 

Milliseconds Duration

Super User
2152 Views
Super User
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"
//
// 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 ) ) ) ) ) )

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

davorgom Frequent Visitor
Frequent Visitor

Re: Milliseconds Duration

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. 

 

 

Highlighted
Super User
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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

davorgom Frequent Visitor
Frequent Visitor

Re: Milliseconds Duration

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

vissvess Member
Member

Re: Milliseconds Duration

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