Forum Discussion

cfed's avatar
cfed
New Member
1 year ago
Solved

Date/time including ms

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 i...
date time format
  • AntrikshSharma's avatar
    AntrikshSharma
    1 year ago

    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
)
  • lbendlin's avatar
    lbendlin
    1 year ago

    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"))
AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
1 year ago

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's avatar
cfed
New Member
1 year ago

Thankyou thats perfect