Time Duration including milliseconds

[sshriner]

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.

[therealfzoccara]

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

[sshriner]

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?

[Anonymous]

Hi @sshriner,

What is the data type of these columns when you import them into Power BI Desktop and how they display in Power BI desktop?

Also check the following blog which is about time duration in Power BI Desktop.

http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

Thanks,
Lydia Zhang

[sshriner]

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.

[Anonymous]

Hi @sshriner,

Based on my test, Power BI desktop cannot properly handle the milliseconds. As a workaround to your scenario, you can split the Time part from your column in your data source, convert the time to number of milliseconds in new columns, then import new columns into Power BI Desktop which should be defined as numeric data type, then calculate duration.

Thanks,
Lydia Zhang

[Anonymous]

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