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 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

 

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"))

7 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community 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

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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.

  • cfed's avatar
    cfed
    New Member
    1 year ago

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

        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