NZ TIMEZONE

[Anonymous]

Hello everyone, everything good?

I have a field called opening date and I would like to convert this field to the New Zealand time zone, but I have to consider the daylight saving.

IT begins at 02:00 NZST on the last Sunday in September each year, and ends at 02:00 NZST on the first Sunday in April.

Is it possible to create a DAX code to calculate this?

[v-ljerr-msft]

Hi @Anonymous,

Here is a good article about how to solve Time Zone Issue in Power BI. And the Method 3 – Web Query with Power Query mentioned in that article is a solution to solve the Day Light Saving issue, could you go to check if it works in your scenario? 

Regards

[Anonymous]

Hello @v-ljerr-msft

I tryed to understand it, but i need your help please

I have one table called "Tickets" and a filed called "Created Date"

I need to change the "Created Date" in (UTC) to a "Created Date" in NZTM.

Could you help me to adapt the code below please?

//let

    Source = Web.Page(Web.Contents("http://localtimes.info/Oceania/New_Zealand/Auckland/")),

    Data1 = Source{1}[Data],

    #"Changed Type" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}}),

    date = #"Changed Type"{1}[Column2],

    time=#"Changed Type"{0}[Column2],

    datetime=DateTime.FromText(date&" "&time)

in

    datetime//

[v-ljerr-msft]

Hi @Anonymous,

I may misunderstand your requirement previously. Based on my research, the solution below should work in your scenario. 

1. First set your local timezone to NZTM on your PC and sets the "Created Date" column to be datetime data type in Query Editor.
2. Then create a new column called UTC and then takes the values in the "Created Date" column and converts them to datetimezone values, using the DateTime.AddZone() function to add a time zone offset of 0 hours, making them UTC times.
3. Finally create a column called Local and converts the UTC times to your PC’s local time zone using the DateTimeZone.ToLocal() function.

For more details about this solution, you can refer to the following article: https://blog.crossjoin.co.uk/2017/03/28/daylight-saving-time-and-time-zones-in-m/

Regards

[philspokas]

Noting this is the Desktop forum, be careful if you publish the the service as ToLocal() will return UCT no matter where you are.

[MarcelBeug]

You can check this video explaining a Power Query function that converts UTC to a local datetime based on a DST pattern.

You can find a link to the PBIX file below the video.