Forum Discussion

sshriner's avatar
sshriner
Advocate I
9 years ago

Time Duration including milliseconds

Hoping someone can help.  I have a table with transaction start times that have minutes, seconds and milliseconds.  i have not been able to find a way to calculate or display a duration that includes milliseconds.  Any help would be appreciated.  Thx.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi sshriner,

    Could you please share sample data of your table and post expected result here? Based on my test, Power BI Desktop doesn’t display milliseconds in its original format, it displays time column containing milliseconds to the following format.


    You can directly substract time values of two columns. Assume that you have two DateTime columns, you can split Date and Time part and calculate them seperately. There is an example for your reference, for more details about what functions are used to calculate duration, please review the example in the attached PBIX file.


    Thanks,
    Lydia Zhang

  • sshriner's avatar
    sshriner
    Advocate I
    9 years ago

    Thanks, Lydia.  I attached a snapshot of my data prior to importing into Power BI.  I saw that the date/time data type is stored internally as a decimal number with an accuracy of 3.33 milliseconds.   Can I calculate duration using the decimal number instead?

     

      • sshriner's avatar
        sshriner
        Advocate I
        9 years ago

        I've seen that post, thank you.

         

        When I import the data into Power BI it comes over as a data/time data type, but the milliseconds are dropped from the display.  i can calculate duration, but the milliseconds are either dropped or rounded, so my durations are not accurate when compared to my original data.

  • therealfzoccara's avatar
    therealfzoccara
    New Member
    2 years ago

    From a duration column (displayed in visuals as a decimal) I'd create a measure, this measure aggregate the duration values and display it as a readable column, here the measure code:

     

     

     

    Timing =
VAR dur = AVERAGE('Fact Tempi'[Durata])
VAR hours = INT ( MOD ( dur * 24 , 24 ) )
VAR minutes = INT ( MOD ( dur * 24 * 60, 60 ) )
VAR seconds = INT ( MOD( dur * 24 * 60 * 60, 60))
VAR millis  = INT ( MOD ( dur * 24 * 60 * 60 * 1000, 1000 ))

VAR hoursText =
    IF (
        LEN ( hours ) = 1,
        CONCATENATE ( "0", hours ),
        CONCATENATE ( "", hours )
    )
VAR minutesText =
    IF (
        LEN ( minutes ) = 1,
        CONCATENATE ( "0", minutes ),
        CONCATENATE ( "", minutes )
    )
VAR secondsText =
    IF (
        LEN ( seconds ) = 1,
        CONCATENATE ( "0", seconds ),
        CONCATENATE ( "", seconds )
    )
VAR millisText =
    IF (
        LEN ( millis ) = 3,
        CONCATENATE ( "", millis ),
        IF (
            LEN ( millis ) = 1,
            CONCATENATE ( "00", millis ),
            CONCATENATE ( "0", millis )
        )
    )
RETURN
    hoursText & ":" & minutesText & ":" & secondsText & "." & millisText