{"id":3871,"date":"2021-12-27T11:49:02","date_gmt":"2021-12-27T19:49:02","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3871"},"modified":"2021-12-28T22:14:49","modified_gmt":"2021-12-29T06:14:49","slug":"how-to-convert-utc-to-local-datetime-in-power-bi","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3871","title":{"rendered":"How To Convert UTC to Local DateTime in Power BI"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Solutions 1: Convert Time Zones &#8211; FORMAT<\/h2>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/natechamberlain.com\/wp-content\/uploads\/2017\/10\/timezonecorrection.png?resize=484%2C168&amp;ssl=1\" alt=\"timezonecorrection.png\" class=\"wp-image-3969\"\/><\/figure><\/div>\n\n\n\n<p>Even if your SharePoint site\u2019s regional settings are correct (or whichever data source you\u2019re pulling from), Power BI could convert it to the wrong time zone upon import. It\u2019s a quick fix, luckily. Instead of using your \u201cmodified,\u201d \u201ccreated\u201d or other date fields in your report, we\u2019ll create a new calculated column in Power BI to use with an accurate time zone.<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>In the Data tab of Power BI, create a new column in your data source (<strong>not a new measure)<\/strong><br><img decoding=\"async\" width=\"138\" height=\"101\" srcset=\"https:\/\/i0.wp.com\/natechamberlain.com\/wp-content\/uploads\/2017\/10\/newcolumn.png?w=274&amp;ssl=1 274w, https:\/\/i0.wp.com\/natechamberlain.com\/wp-content\/uploads\/2017\/10\/newcolumn.png?resize=150%2C109&amp;ssl=1 150w, https:\/\/i0.wp.com\/natechamberlain.com\/wp-content\/uploads\/2017\/10\/newcolumn.png?resize=100%2C73&amp;ssl=1 100w, https:\/\/i0.wp.com\/natechamberlain.com\/wp-content\/uploads\/2017\/10\/newcolumn.png?resize=250%2C182&amp;ssl=1 250w\" src=\"https:\/\/i0.wp.com\/natechamberlain.com\/wp-content\/uploads\/2017\/10\/newcolumn.png?resize=138%2C101&amp;ssl=1\" alt=\"newcolumn\"><\/li><li>Enter the following equation, replacing red text with your unique data:NewColumnName&nbsp;= <strong>FORMAT(DataTableName[ColumnName] \u2013 TIME(5,0,0), \u201cGeneral Date\u201d)<\/strong> <strong>For example:<br><em>LocalTime = FORMAT(Tweets[Created]-TIME(7,0,0),\u201dGeneral Date\u201d)<\/em><\/strong><\/li><li>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\u2019ve set it up.<br><\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image size-full\"><img fetchpriority=\"high\" decoding=\"async\" width=\"865\" height=\"219\" src=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2021\/12\/Format1.png\" alt=\"\" class=\"wp-image-3874\" srcset=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2021\/12\/Format1.png 865w, https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2021\/12\/Format1-300x76.png 300w, https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2021\/12\/Format1-768x194.png 768w\" sizes=\"(max-width: 865px) 100vw, 865px\" \/><figcaption>This is another example with the EST (-5) timezone<\/figcaption><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Update your report to use this new column in place of your original date\/time column.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Solutions 2: Convert Time Zones &#8211; DateTimeZone.SwitchZone<\/h2>\n\n\n\n<p>By&nbsp;Ali Sharifi<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">DATE\/TIME \u2013 DATE\/TIME\/TIMEZONE<\/h3>\n\n\n\n<p>There are 2 datatypes in Power Query that are used for storing date and time values:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Date\/Time&nbsp;<\/li><li>Date\/Time\/TimeZone<\/li><\/ul>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/cloudbi.com.au\/wp-content\/uploads\/2019\/12\/P901.png\" alt=\"\" class=\"wp-image-5518\"\/><\/figure><\/div>\n\n\n\n<p>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. &nbsp; For example, Sydney Australia in winter uses&nbsp;Australian Eastern Standard Time (AEST) which is 10 hours ahead of&nbsp;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.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/cloudbi.com.au\/wp-content\/uploads\/2019\/12\/p902.png\" alt=\"\" class=\"wp-image-5519\"\/><\/figure><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">HOW TO CONVERT UTC TO LOCAL TIME<\/h3>\n\n\n\n<p>For this exercise, I create a simple table with 2 columns. TimeGenerated is the&nbsp;Date\/Time\/TimeZone&nbsp;column which contains the UTC time and I am going to change it to local time.&nbsp;&nbsp;&nbsp;&nbsp;<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/cloudbi.com.au\/wp-content\/uploads\/2019\/12\/p903.png\" alt=\"\" class=\"wp-image-5520\"\/><\/figure><\/div>\n\n\n\n<p><br>Let\u2019s create a duplicate of TimeGenerated and change its data type to DateTime:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/cloudbi.com.au\/wp-content\/uploads\/2019\/12\/p904.png\" alt=\"\" class=\"wp-image-5521\"\/><\/figure><\/div>\n\n\n\n<p>The data in the duplicated column has now changed to local time(+11:00) and it looks like we have completed the task!<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/cloudbi.com.au\/wp-content\/uploads\/2019\/12\/P905.png\" alt=\"\" class=\"wp-image-5522\"\/><\/figure><\/div>\n\n\n\n<p><br>But this conversion has used my machine\u2019s 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:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/cloudbi.com.au\/wp-content\/uploads\/2019\/12\/P906.png\" alt=\"\" class=\"wp-image-5523\"\/><\/figure><\/div>\n\n\n\n<p><br>This custom column works in both Power BI Desktop and Service:&nbsp;<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/cloudbi.com.au\/wp-content\/uploads\/2019\/12\/P910.png\" alt=\"\" class=\"wp-image-5527\"\/><\/figure><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">INCLUDING DAYLIGHT SAVING TIME<\/h3>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>First create a table named DST and enter start and end time of daylight saving times for the years your data belongs to:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/cloudbi.com.au\/wp-content\/uploads\/2019\/12\/P907.png\" alt=\"\" class=\"wp-image-5524\"\/><\/figure><\/div>\n\n\n\n<p>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.&nbsp;<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/cloudbi.com.au\/wp-content\/uploads\/2019\/12\/P908.png\" alt=\"\" class=\"wp-image-5525\"\/><\/figure><\/div>\n\n\n\n<p>The last part is changing the formula of the custom column and adding the result of the function to the time difference (+10):&nbsp;<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/cloudbi.com.au\/wp-content\/uploads\/2019\/12\/p909.png\" alt=\"\" class=\"wp-image-5526\"\/><\/figure><\/div>\n\n\n\n<p>Ref: https:\/\/cloudbi.com.au\/converting-utc-to-local-datetime-in-power-bi\/<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Solutions 1: Convert Time Zones &#8211; FORMAT Even if your SharePoint site\u2019s regional settings are correct (or whichever data source you\u2019re pulling from), Power BI could convert it to the wrong time zone upon import. It\u2019s a quick fix, luckily. Instead of using your \u201cmodified,\u201d \u201ccreated\u201d or other date fields in your report, we\u2019ll create <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3871\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[1014,1015,1016],"class_list":["post-3871","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-convert-utc-to-local-datetime","tag-convert-utc-to-other-datetime","tag-datetimezone-switchzone"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3871","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3871"}],"version-history":[{"count":5,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3871\/revisions"}],"predecessor-version":[{"id":3887,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3871\/revisions\/3887"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3871"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3871"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3871"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}