Convert Date Column from UTC to EST Timezone

[Anonymous]

Hi, 

I'm pulling CRM dynamics data in UTC format, I see that dates showing in Powerbi is different from CRM dynamics. Now i want to convert that date column to local time zone EST. 

Please share me in Power Query how to convert this Date column from UTC to EST time zone

Here is the sample data looks like -

Thanks,

Santosh

[Syndicate_Admin]

@Syndicate_Admin , I am looking for the same solution that auto process Day time saving as well.

So far this is semi-manual way I found 

Add a Refresh Time Stamp To Your Power BI Reports — ehansalytics

[lmyers]

I'm using the powerbi template - CQ and AA analytics combined analytics. The call queue analysis table has time in UTC. How do I add a column, conversion to conver this to EST? When filtering on the measure Calls volumne for a day, the time is in UTC which is not helpful for our staff when they are looking at what time calls are coming in.

I'm very new to powerbi and would like some assistance how I can add a column to this table to convert the Date column to a Date column in EST.

I've tried to follow these suggestions but not sure how to apply this to the template.

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

UTC is 5 hrs ahead of EST,so create below M query to convert the timezone column to EST time:

EST = DateTimeZone.SwitchZone([UTC],-5)

And you will see:

For the sample .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[JacoMathew]

Hi,

how does the function take care when the day light savings kicks in ? Is there a function that looks at the date and decides whether it should be -5 or -4 (Eastern Daylight Time) ?

Thanks

Jaco

[Jimmy801]

Hello @Anonymous 

you can use the function DateTimeZone.SwitchZone. Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTXMzTUMzIwMlAwMLACIwVtMKUUqwOUNsQtHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Created_Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created_Date", type datetimezone}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "EST", each DateTimeZone.SwitchZone([Created_Date], 5), type datetimezone)
in
    #"Added Custom"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy