How to use milliseconds from a DateTime column

[mja_]

Hi,

I have one column createTime within a SQL database. When I import it, the column is formated as Date/Time and displayed as "01/03/2023 14:54:10" and when I click on this value I see it formated as "2023-03-01T14:54:09.8770000" including the milliseconds. I need the milliseconds within the dashboards for evaluations and comparisions. Right now, when I create a visual the field createTime does not display the milliseconds.

I tried to split date and time, chose a diffrent format, transform it etc. but lost the milliseconds everytime.

Any ideas?

[v-yueyunzh-msft · Accepted solution]

Hi, @mja_ 

According to your descripition, the raw data is the "2023-03-01T14:54:09.8770000", and when you load the data in Power BI , the milliseconds will go away.

After my test, all date formats in Power BI currently ignore milliseconds.

For your needs, if you want to compare the size of dates, you can try to create a new column to achieve.

First, we need to make the datetime column to the Text Type.

Click on the Navigation step and then click on Add Column -> Custom Column. A prompt will pop up asking if you want to insert a step, click Insert.

In the prompt, enter the following formula.

Text.Start([Column1], 4) & 
Text.Middle([Column1], 5, 2) & 
Text.Middle([Column1], 8, 2) & 
Text.Middle([Column1], 11, 2) & 
Text.Middle([Column1], 14, 2) & 
Text.Middle([Column1], 17, 2) & 
Text.Middle([Column1], 20, 7)

This gives you a column that can be used to sort the items in the table by a precise datetime.

If you want to perform some calculations on your millisecond column, then you may need to add a millisecond column in the SQL SERVER layer and then load the data to Power BI  to meet your needs.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lucadelicio]

I'm not sure that in pbi u can use the millisec.

Try to split in sql in a separate column and import in pbi.