Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
We had been struggling with converting UTC times to local time with daylight savings in the Power BI service. We didnt find any of the solutions here or in linked blogs worked for us. We had a ticket open with support for over 2 months and eventually learned it's not supported.
You'll see there are some solutions that say it is supported when using DateTimeZone.ToLocal(). However, if the dataset refreshes in the service it will revert to UTC. Per support, this is expected behavior.
We came up with an M query to workaround the issue and thought we'd share. I'm sure there's a better way to do this, probably with DAX, but this is a quick/dirty workaround at least. I'll post it in a reply so it can be marked answered.
Solved! Go to Solution.
To convert a date/time stored in UTC datetimezone type to local time (ours is called datetimeUTC), including daylight savings :
Text.Combine(
{Text.From(Date.Month([datetimeUTC])),
Text.PadStart(Text.From(Date.Day([datetimeUTC])),2,"0"), Text.PadStart(Text.From(Text.From(Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Hour"))),2,"0")
})
//create one entry for each year that exists in the dataset
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2018
then
//set the start/end of daylight savings for that year.
// (first number is the start, second is the end)
//this is in UTC time in the format of MMDDHH
if [MonthDayHour] >= 31109 and [MonthDayHour] < 110408
//set the UTC offset if daylight savings is active
then DateTimeZone.SwitchZone([datetimeUTC],-6)
//set the UTC offset if daylight savings is not active
else DateTimeZone.SwitchZone([datetimeUTC],-7)
else
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2019
then
if [MonthDayHour] >= 31009 and [MonthDayHour] < 110308
then DateTimeZone.SwitchZone([datetimeUTC],-6)
else DateTimeZone.SwitchZone([datetimeUTC],-7)
else
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2020
then
if [MonthDayHour] >= 30809 and [MonthDayHour] < 110108
then DateTimeZone.SwitchZone([datetimeUTC],-6)
else DateTimeZone.SwitchZone([datetimeUTC],-7)
else
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2021
then
if [MonthDayHour] >= 31409 and [MonthDayHour] < 110708
then DateTimeZone.SwitchZone([datetimeUTC],-6)
else DateTimeZone.SwitchZone([datetimeUTC],-7)
//set the default if no year is defined
else DateTimeZone.SwitchZone([datetimeUTC],-7)
To convert a date/time stored in UTC datetimezone type to local time (ours is called datetimeUTC), including daylight savings :
Text.Combine(
{Text.From(Date.Month([datetimeUTC])),
Text.PadStart(Text.From(Date.Day([datetimeUTC])),2,"0"), Text.PadStart(Text.From(Text.From(Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Hour"))),2,"0")
})
//create one entry for each year that exists in the dataset
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2018
then
//set the start/end of daylight savings for that year.
// (first number is the start, second is the end)
//this is in UTC time in the format of MMDDHH
if [MonthDayHour] >= 31109 and [MonthDayHour] < 110408
//set the UTC offset if daylight savings is active
then DateTimeZone.SwitchZone([datetimeUTC],-6)
//set the UTC offset if daylight savings is not active
else DateTimeZone.SwitchZone([datetimeUTC],-7)
else
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2019
then
if [MonthDayHour] >= 31009 and [MonthDayHour] < 110308
then DateTimeZone.SwitchZone([datetimeUTC],-6)
else DateTimeZone.SwitchZone([datetimeUTC],-7)
else
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2020
then
if [MonthDayHour] >= 30809 and [MonthDayHour] < 110108
then DateTimeZone.SwitchZone([datetimeUTC],-6)
else DateTimeZone.SwitchZone([datetimeUTC],-7)
else
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2021
then
if [MonthDayHour] >= 31409 and [MonthDayHour] < 110708
then DateTimeZone.SwitchZone([datetimeUTC],-6)
else DateTimeZone.SwitchZone([datetimeUTC],-7)
//set the default if no year is defined
else DateTimeZone.SwitchZone([datetimeUTC],-7)
I can't believe we still have to build in these hacks.... Especially when using Power BI and Sharepoint together!
Anyhow, this is a good enough solution for me to get the job done for short periods of time. Thanks for posting 😃
Here we are 2.5 years later and this is still a major problem. I'm (CST time zone) building a report for a facility in Eastern time, however their SharePoint is configured to eastern time with an offset of -5 hours from UTC, however they are actually in daylight savings time with an offset of -4. Handling this in Power BI is an utter nightmare. I can't change the timezone in Power Query, because it modifies the time.