Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

NZ TIMEZONE

 

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?

Need Help

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    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? :smileyhappy:

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,

         

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

        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