DAX to convert Epoch to DateTime

[Anonymous]

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

[BruGonzo3]

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]

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)

[Anonymous]

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]

This logic is not working in my case

[Anonymous]

Greetings,

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

[MarcelBeug]

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.

[Anonymous]

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

[MarcelBeug]

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.

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

[v-srgoda]

Hey Thanks, 

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

saved the time for me 🙂 

[tuxsax]

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:

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

Thanks!

Ziv

[tuxsax]

No answer? anyone?

I'll really appreciate some help...

[tuxsax]

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