Date/time including ms

[cfed]

Hi All,

I have a date time column where the raw data is decimal time in ecel format ie 45433.973275463 (which is 21/05/2024 13:00:27.19)

In power bi I can not by defualt display the ms (.19 in the above example).

As I need the column for some relationships and time intelligence i am forced to make a duplicate column and convert the decimal value into hh:mm:ss.00. Is there a simple way to do this?

Thanks for your help.

Chris

[AntrikshSharma · Accepted solution]

@cfed Try this:

ADDCOLUMNS ( 
    SELECTCOLUMNS ( 
        { 45433.973275463, 45498.962265625 },
        "Date", [Value]
    ),
    "DateTime", 
        VAR DatePart = INT ( [Date] )
        VAR TimePart = [Date] - DatePart
        VAR TotalSeconds = TimePart * 86400
        VAR SecondsEveryHour = 3600
        VAR Hours = INT ( TotalSeconds / SecondsEveryHour )
        VAR Minutes =  INT ( DIVIDE ( MOD ( TotalSeconds, SecondsEveryHour ), 60 ) )
        VAR Seconds = MOD ( TotalSeconds, 60 )
        VAR Result = 
            FORMAT ( DatePart, "YYYY-MM-DD" ) 
                & " " & FORMAT ( Hours, "00" )
                & ":" & FORMAT ( Minutes, "00" )
                & ":" & FORMAT ( Seconds, "00.00" )
        RETURN Result
)

[AntrikshSharma · Accepted solution]

@cfed Try this:

ADDCOLUMNS ( 
    SELECTCOLUMNS ( 
        { 45433.973275463, 45498.962265625 },
        "Date", [Value]
    ),
    "DateTime", 
        VAR DatePart = INT ( [Date] )
        VAR TimePart = [Date] - DatePart
        VAR TotalSeconds = TimePart * 86400
        VAR SecondsEveryHour = 3600
        VAR Hours = INT ( TotalSeconds / SecondsEveryHour )
        VAR Minutes =  INT ( DIVIDE ( MOD ( TotalSeconds, SecondsEveryHour ), 60 ) )
        VAR Seconds = MOD ( TotalSeconds, 60 )
        VAR Result = 
            FORMAT ( DatePart, "YYYY-MM-DD" ) 
                & " " & FORMAT ( Hours, "00" )
                & ":" & FORMAT ( Minutes, "00" )
                & ":" & FORMAT ( Seconds, "00.00" )
        RETURN Result
)

[cfed]

Thankyou thats perfect

[cfed]

Thankyou for your reply. 

how exactly do I use FORMAT to show the ms? That is the bit I am struggling with.

Thanks

Chris

[lbendlin]

45433.973275463 (which is 21/05/2024 13:00:27.19)

it clearly isn't - .97 is towards the end of the day.  Your value also has no milliseconds.

Table 2 = ADDCOLUMNS({45433.973275463},"r",FORMAT([Value],"hh:nn:ss") & Format([Value]*86400-ROUNDDOWN([Value]*86400,0),".00"))

[cfed]

Hi Thanks for your reply, sorry my example was rubbish. You've helped me get most of the way there however it is not always working.

This example causes an issue.

45498.962265625

This should create the following time stamp

25/07/24 23:05:39.75

But instead in power bi i get:

25/07/24 23:05:40.75

1 second error

What do you think is causing this?

Thanks again for your help

Chris

[lbendlin · Accepted solution]

Ah, that is very subtle indeed.  Power BI is rounding even when explicitly told not to.

Table 2 = ADDCOLUMNS({45498.962265625},"r",var rd = ROUNDDOWN([Value]*86400,0) Return FORMAT(rd/86400,"hh:nn:ss") & Format([Value]*86400-rd,".00"))

[lbendlin]

Keep your decimal value and do all your calculations. Then when you need to render the result use FORMAT to show the required formatting including milliseconds.