Forum Discussion
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...
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 )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"))
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
Super User
Super UserAh, 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"))