Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to convert Milliseconds into a DateTime format?

I currently have a table of stored API data. Below I'm showing one seperate column with a few example rows that contain 13 digit Millisecond timestamps. How would I convert this into a DateTime format? Or just any readable Time or Date format that makes it look nice. Even if it's creating a custom column that transforms the data which I have tried to do but I've had no success. 

Any help would be appreciated. 

Created
1592397519540
1592401918076
1592412872330
1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @Anonymous 

Download a sample PBIX file with data and code

Try the measure like this

Measure = DATEVALUE("1970-1-1") + MAX('Table'[Created]) /(60*60*24*1000)

To give this

ms.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

6 REPLIES 6
PhilipTreacy
Super User
Super User

Hi @Anonymous 

Download a sample PBIX file with data and code

Try the measure like this

Measure = DATEVALUE("1970-1-1") + MAX('Table'[Created]) /(60*60*24*1000)

To give this

ms.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

Thank you @PhilipTreacy 

This works! I appreciate you going through it in simple detail for me. 

PhilipTreacy
Super User
Super User

Hi @Anonymous 

Measures are written in the DAX language and need to be created from within the Data view of PBI Desktop.

From your screenshot it looks like you are writing the DAX in the Power Query editor but this only accepts the M language.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

Thank you @PhilipTreacy , I got confused. I'm currently creating a new measurement however, I can't direct it to just one column. 

Measure = DATEVALUE("1970-1-1") + ('Created' /(60*60*24*1000))

On here the 'Created' part is asking for the table instead of the column. How do I make it specific for the 'Created' column? 

PC2790
Community Champion
Community Champion

Hi @Anonymous ,

 

See a similar thread: https://community.powerbi.com/t5/Desktop/How-Can-I-Convert-Milliseconds-Value-into-Date/td-p/361099

 

The measure suggested is : Measure = DATEVALUE("1970-1-1") + ('Created' /(60*60*24*1000))

Try and see if it works for you

Anonymous
Not applicable

Hello @PC2790 

Thanks for the suggestion, I did already go through that answer and the "Measure" wasn't recognised, neither was "DATEVALUE" when I tried to create a custom column of [Created] data. 

KyleAdam_0-1611388012032.png

KyleAdam_1-1611388228649.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.