Solutions 1: Convert Time Zones – FORMAT
Even if your SharePoint site’s regional settings are correct (or whichever data source you’re pulling from), Power BI could convert it to the wrong time zone upon import. It’s a quick fix, luckily. Instead of using your “modified,” “created” or other date fields in your report, we’ll create a new calculated column in Power BI to use with an accurate time zone.
- In the Data tab of Power BI, create a new column in your data source (not a new measure)
- Enter the following equation, replacing red text with your unique data:NewColumnName = FORMAT(DataTableName[ColumnName] – TIME(5,0,0), “General Date”) For example:
LocalTime = FORMAT(Tweets[Created]-TIME(7,0,0),”General Date”) - Then, under the modeling tab in the formatting section, you can format the calculated column to display data in any number of ways. This is how data will show up for tooltips and perhaps the report itself depending on how you’ve set it up.
- Update your report to use this new column in place of your original date/time column.
Solutions 2: Convert Time Zones – DateTimeZone.SwitchZone
By Ali Sharifi
Nowadays many systems such as cloud services and IOT devices that work globally prefer to use the UTC time zone to be consistent across different countries. When it comes to reporting, however, the end-user likes to see the DateTime values in their own local time zone. This post shows you how to convert DateTime values from UTC to local time.
DATE/TIME – DATE/TIME/TIMEZONE
There are 2 datatypes in Power Query that are used for storing date and time values:
- Date/Time
- Date/Time/TimeZone
The latter type has the addition of a timezone which shows the difference to UTC time in hours and minutes. Some places around the world have 2 different time zones due to daylight saving. For example, Sydney Australia in winter uses Australian Eastern Standard Time (AEST) which is 10 hours ahead of UTC, and in summer it uses Australian Eastern Daylight Time(AEDT) which is 11 hours ahead of UTC. Below, you can see that the LocalDateTimeZone column that is Date/Time/Timezone includes +11:00 which is the time difference to UTC.
HOW TO CONVERT UTC TO LOCAL TIME
For this exercise, I create a simple table with 2 columns. TimeGenerated is the Date/Time/TimeZone column which contains the UTC time and I am going to change it to local time.
Let’s create a duplicate of TimeGenerated and change its data type to DateTime:
The data in the duplicated column has now changed to local time(+11:00) and it looks like we have completed the task!
But this conversion has used my machine’s local time zone, if I published the report to Power BI Service and refresh the data there instead, the data model would contain the times at UTC because the Power BI Service uses UTC as the default time zone. The correct solution is using the SwitchZone function and creating a custom column to calculate the local time:
This custom column works in both Power BI Desktop and Service:
INCLUDING DAYLIGHT SAVING TIME
In the previous step we added +11 hours to UTC to have Sydney time but this is only for the summer when daylight saving time is applied. In the winter the difference is 10 hours. For handling this, we must find out whether the date is in DST period or not and add the corresponding hours to UTC.
First create a table named DST and enter start and end time of daylight saving times for the years your data belongs to:
Then create a custom function named DSTCheck which gets a UTC DateTime value as an input parameter and returns 1 if the date in the DTS period and 0 if it is not.
The last part is changing the formula of the custom column and adding the result of the function to the time difference (+10):
Ref: https://cloudbi.com.au/converting-utc-to-local-datetime-in-power-bi/