Convert data from UTC to Local Time

[Anonymous]

Hello Everyone,

I know this issue has been brought up many times.

Post in community is mostly rely on simply add | minus some hours

https://community.powerbi.com/t5/Desktop/Convert-Date-Column-from-UTC-to-Central-Time/m-p/818704#M39...

https://community.powerbi.com/t5/Desktop/Custom-Query-Function-to-convert-UTC-to-EST/m-p/1106780#M51...

I am in New Zealand, we have daylight saving on winter, and daylight-saving time is based on criteria as "First Sunday in April" making logic very complicate.

Is there a elegant way of achieving UTC to NZDT/NZST?

Best

[AlexisOlson]

If your input is in DateTimeZone format already, then you might be able to use DateTimeZone.ToLocal.

If not, then you can probably use DateTime.AddZone as explained here:

https://community.powerbi.com/t5/Desktop/Convert-utc-to-local-time-zone-using-Power-Query/td-p/45533

[Anonymous]

@AlexisOlson Hello Alexis,

Not sure I got it correct or not, based on my understanding,

#datetimezone(https://docs.microsoft.com/en-us/powerquery-m/sharpdatetimezone)

requires a predefined  offset-hours + offset-minutes 

The complicate case here is that offset-hour is not a fixed value, it changes twice a year... same applies to DateTime.AddZone

[Anonymous]

Hi @Anonymous ,

Please review the following documentations, hope they can help you.

Convert UTC to Local Time with Daylight Savings Support in Power BI

4 Ways to Fix Date Errors in Power Query + Locale & Regional Settings

Local date formats in Power BI

Best Regards

Rena

[Anonymous]

Thank you for your reply.

Basically the ideas of the post is still manually define when to switch the exact hours on difference.

The complexity comes from first sunday of certain month...

there is a big chuck of logical code written to detect first sunday of some month.
I know how to achieve just think it would be nice to add a easier timezone convert function, which can be a more elegant method.

[Greg_Deckler]

@Anonymous - I have a time zone solution in my book DAX Cookbook, it is Recipe 9 I think of Chapter 3. https://github.com/gdeckler/DAXCookbook

Also, there is a DAX function's UTCNOW and UTCTODAY and in Power Query there are methods as well: https://community.powerbi.com/t5/Community-Blog/How-To-Convert-UTC-to-Your-Local-Time-Zone-in-Power-Query/ba-p/976650