Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fn-craig
Frequent Visitor

Handling local time zone with Daylight Savings in Power BI Service

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.

1 ACCEPTED SOLUTION
fn-craig
Frequent Visitor

To convert a date/time stored in UTC datetimezone type to local time (ours is called datetimeUTC), including daylight savings :

 

  • Create a new column "MonthDayHour" from the UTC datetimetimezone that shows the month/day/hour. Convert to whole number type.

 

 

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 a new column for LocalTime (datetimezone type) that sets your local offset based on the time of year. This is for Mountain Time. It needs an entry for every year, this only is for 2018-2021. To add additional years, just lookup what date/time (in UTC) DST starts/ends.

 

 

//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 chose to use two columns so it didnt have to do the text combining and record parsing for every row on every if/then. You could so this in one column as well. I'm sure there's a better way to do this either with a lookup table or with DAX, but this was our quick and dirty fix and we didnt see another one posted.

View solution in original post

3 REPLIES 3
fn-craig
Frequent Visitor

To convert a date/time stored in UTC datetimezone type to local time (ours is called datetimeUTC), including daylight savings :

 

  • Create a new column "MonthDayHour" from the UTC datetimetimezone that shows the month/day/hour. Convert to whole number type.

 

 

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 a new column for LocalTime (datetimezone type) that sets your local offset based on the time of year. This is for Mountain Time. It needs an entry for every year, this only is for 2018-2021. To add additional years, just lookup what date/time (in UTC) DST starts/ends.

 

 

//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 chose to use two columns so it didnt have to do the text combining and record parsing for every row on every if/then. You could so this in one column as well. I'm sure there's a better way to do this either with a lookup table or with DAX, but this was our quick and dirty fix and we didnt see another one posted.
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors