cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DAX to convert Epoch to DateTime

I have just imported a bunch of data with epoch timestamps from the server. 

 

Does anyone have a DAX expression that can convert this into DateTime format for PBI?

 

Example epoch time value: 1486933998060

 

Ryan

12 REPLIES 12
BruGonzo3
New Member

Using DAX

SPECIAL NOTE: The Epoch dates in this example are always UTC-3 due to my source system so I have an extra portion to my formula of “-TIME(3,0,0)” to take away those 3 hours.  You may need to handle the time difference in some other way (maybe related to timezones).

This method requires creating a measure and an extra column in your table.

Start by adding a new MEASURE with just the 1970-01-01 date. I called my Measure Staring_1970_DT:

Staring_1970_DT = DATEVALUE("01/01/1970")

Then I added a new column (NewDateTime) to the table which becomes my new date time column based on the Epoch column “CREATE_DATE” in my Table called HelpDeskCalls:

NewDateTime = ([Staring_1970_DT]+(HelpDeskcalls[CREATE_DATE]/60/60/24))-TIME(3,0,0)

MarcelBeug
Community Champion
Community Champion

Don't know how to do it in DAX. In Power Query it can be done, but your example looks 1000x too high, so that would become:

#datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Epoch]/1000)

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Thank you, the example includes the time zone which is why it might appear longer. 

 

How do you apply this power query? do you just 'Insert Step After' in the Applied Steps and add the query there?

 

I did this and changed [Epoch] to my column name but it errored out.



= #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Epoch]/1000)

 

I'm sure thats wrong as I need to include the expression in someother function right?

Anonymous
Not applicable

This logic is not working in my casepowerbi error.JPG

Anonymous
Not applicable

Greetings,

 

Please let me know how to convert filetime to date time in power bi

Example code:

 

let
    Source = #table({"Epoch"},{{1486933998060}}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Epoch]/1000))
in
    #"Added Custom"

 

And this video shows how it was created.

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Thanks for the vid Marcel. Thats great. Its all working. 

 

Its going into UTC though. How do I pull the timezone data using the M query?

 

eg check 1486933894289 on http://www.esqsoft.com/javascript_examples/date-to-epoch.htm

GMT+1030 will pop out of the conversion. 

 

Can we produce this in power query too? ie make the column value local not UTC

With me, UTC +01:00 pops out...

 

Power Query has basic functionality to account for timezones.

 

I adjusted the value (...060 --> ...000) and the code so it will make it a UTC datetimezone value instead of a datetime value.

I added 2 options:

 

1. Conversion to local time.

This is dependent on the systems date/time zone, which may be risky if you publish reports to the web and it may run on a server in any timezone.

 

2. Conversion to a fixed UTC offset (i.c. UTC +10:30).

This is suitable if the destination time zone has no Daylight Saving Time switches (or other clock switches due to government decisions). So if you are in the Adelaide time zone (aren't you?), then the clock will switch back at April 1, 4:30 PM UTC to April 2, 2:00 AM (UTC +09:30).

 

If you want to be at the save side, you may consider creating yourself a table like the one in the picture ("Lokaal" is Dutch for "Local") with clock switches and use this to convert UTC times to the time in your time zone.

 

Coincidentally I'm that guy that created such files for all 130 Windows time zones and consolidated those file in 1 file which allows conversion of date/times between 1/1/2000 and 12/31/2017 from any Windows time zone to any other Windows time zone. I used that as an example about some Power Query functionality (combining binaries) in a video.

 

Clock switches Adelaide.png

 

let
    Source = #table({"Epoch"},{{1486933998000}}),
    AddedUTC = Table.AddColumn(Source, "UTC", each #datetimezone(1970,1,1,0,0,0,0,0)+#duration(0,0,0,[Epoch]/1000), type datetimezone),
    #"Duplicated Column" = Table.DuplicateColumn(AddedUTC, "UTC", "Local"),
    UTCToLocal = Table.TransformColumnTypes(#"Duplicated Column",{{"Local", type datetime}}),
    AddedSwitchZone = Table.AddColumn(UTCToLocal, "SwitchZone", each DateTimeZone.SwitchZone([UTC],10,30))
in
    AddedSwitchZone
Specializing in Power Query Formula Language (M)

Hey Thanks, 

 

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000) //Replace [UnixTime] with columnname

saved the time for me 🙂 

Hi,

That seems to be working, the problem is I don't really understand the exact steps for applying it to my specific case.
I have the following columns:

 

2017-07-20_1855.png

 
I need the exact query that will translate them to regular date/time columns.

Thanks!

Ziv

tuxsax
Regular Visitor

No answer? anyone?

I'll really appreciate some help...

tuxsax
Regular Visitor

Nevermind, I've managed, thanks for your great help!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors