Transforming Dynamics 365 UTC Date/Time to local Daylight Saving and Standard Date/Time in Power BI

Colin Maitland, 04 November 2019

In this article, I demonstrate the transformation of Dynamics 365 UTC Date/Time values to New Zealand (NZ) Local Date/Time and Date values in Power BI, taking into account Daylight Saving Time.

This demonstration highlights a significant difference in the transformation of these values when the Power BI data model is refreshed in Power BI Desktop versus the Power BI Online Service.

NZ Standard Time (NZ ST) is UTC+12 and NZ Daylight Saving Time (NZ DST) is UTC+13. NZL DST starts on the first Sunday of September with clocks going forward 1 hour at 2:00 a.m. and ends on the first Sunday of April with clocks going back 1 hour at 3:00 a.m. In 2019, NZ DST ends on 7th April and starts on 29th September.

The following tables show some Dynamics 365 Created On (UTC) Date/Time values loaded into a Power BI data model and then transformed to Date/Time and Date values using several different transformation approaches.

In the following image, the data model is hosted and has been refreshed in Power BI Desktop on a local workstation where the Time Zone is “(UTC+12) Auckland, Wellington”. The records enclosed in the red rectangles show the Created On (UTC) Date/Time values that fall within NZ DST. The green highlight indicates which of these have been transformed to the correct corresponding NZ Local Date/Time values; i.e. the transformations have applied an offset of +13 hours for NZ DST and +12 hours for NZ ST.

The columns in this table are as follows:

• Created On (UTC): Is the Date/Time as it is stored in Dynamics 365. In Power BI these UTC Date/Time values are Date/Time/Zone values; e.g. 6/04/2019 12:00:00 p.m. +00:00 in the Power BI data view and 6/04/2019 12:00:00 p.m. in the Power BI report view.

• Created On (Date/Time): Is the UTC Date/Time/Zone value transformed to a Date/Time value in Power BI using the Data Type transformation option in Power Query.

• Created On (To Local): is the UTC Date/Time/Zone value transformed to a Local Time value in Power BI using the Local Time transformation option in Power Query.

• Created On (Custom FX): Is the UTC Date/Time/Zone value transformed to NZ Local Date/Time value using a custom function in Power Query.

• Created On (Date): Is the UTC Date/Time/Zone value transformed to a Date value in Power BI using the Data Type transformation option in Power Query.

When published to the Power BI Online Service the same values are initially displayed. This is until the data model is then refreshed.

In the following image, the data model is hosted and has been refreshed in the Power BI Online Service where the Time Zone is “(UTC+00) Universal Co-ordinated Time”. This cannot currently be changed. The yellow highlight indicates which values have not been transformed to the correct corresponding NZ Local Date/Time and Date values. In fact, these are all UTC Date/Time and Date values. They have not been transformed to NZ Local Date/Time and Date values. The only exceptions are the Date/Time values highlighted in green that have been transformed using a custom function in Power BI.

This is an important issue to be aware of. This issue applies to other Time Zones as well. In the Power BI Online Service, the data model is refreshed in the “(UTC+00) Universal Co-ordinated Time” time zone. Therefore Power Query and Data Analysis Expression (DAX) Date/Time and Date transformations and functions such as Now() and Today() are based on that time zone for data models that are refreshed from within the Power BI Service.

It is not best practice to store Dates and Times in the same column in a Power BI data model. They should be split into separate Date and Time columns. Also, the time portion only needs to be retained if it is required for analysis and reporting purposes, otherwise it can be discarded. However, any Time Zone transformation should be performed first in order to ensure that the resulting Date and Time values are correct.

In my next article, I will describe the custom function that has been used to correctly transform the Dynamics 365 UTC Date/Time/Zone values to local Date/Time values. UTC Date/Time/Zone values transformed using this function can then also be transformed to Date values.