Forum Discussion
Date/time including ms
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"))
Thankyou for your reply.
how exactly do I use FORMAT to show the ms? That is the bit I am struggling with.
Thanks
Chris
Super User45433.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.
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
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"))