Power BI service showing UTC date despite local time format

[Ry009]

Hi

For the last 5 months, I've had no issue. I have 20ish different report sources for my Dashboard that I like to display to users with a table with a list of data sources and date refreshed times times. so that there isn't confusion between the systems and exports get refreshed daily.

This table is essentially the 'Source' step of importing SharePoint Data which contains 'Date modified' column and the csv file name.

The only major change since this issue occured was creating a scheduled refresh in Power BI service. I've checked the OS time settings, and browser language settings on multiple browsers, so I know that isn''t the issue.

Additionally what is really weird, that when I publish from Desktop that has the correct time, it will show the local time in the browser, and then revert back to UTC.

Any ideas what is happening here?

[Ry009 · Accepted solution]

For anyone reading, I managed to find a solution that was a good work around for me. I still dont understand why this happened though.

Localtime  = [Your time column here] + TIME(10, 0, 0)

(The 10 adds 10 hours) 

I'll just need to update when Australian Eastern Daylight Savings time kicks in.

[Bipin-Lala · Accepted solution]

Hi @Ry009,

As others have also mentioned, Power BI Service works on UTC time. Hence, using local timezone would display the correct expected time in Power BI Desktop, but once you publish your report on Power BI Service, the servers that host the reports work on UTC time, and hence local timezone would show UTC time when viewing reports online.

Thus, as you already did, you would need to add appropriate hours, like 10 hours in your case to UTC time to display expect time ad per the required timezone.

Although your are on the right path, for handling daylight savings time, you can write a function that can be invoked to convert UTC time to Daylight Savings Time of your required timezone.

The following links would help you with creating the function - 

• https://willpage.dev/2020/06/26/converting-utc-to-new-zealand-time-in-m-power-query/

The function mentioned in the above links works beautifully and helped in coverting UTC times to NZ DST for one of my reports recently.

Just adjust the values for Australia DST and you should be good to go. Let me know if it helps!

[AndresEsteban]

Hello,

Thank you all very much for the collaboration, I am having exactly the same problem, when I publish something in PBI Service it automatically sets UTC. I have tried to do what has been discussed through a function that adapts the data to my time region, the problem is that if this dashboard is consumed in another time region it will not adapt, would anyone know how to adapt the data to each time zone automatically? That is to say, if a user in London accesses the dashboard that the data adapts to their time zone but if the same dashboard is opened by a person in Australia that it also adapts to that time zone.

[Brunner_BI]

When you run USERCULTULRE() in DAX you will get the country and language of a user. That would be an approximation for the time zone, even though it is not 100% reliable because people travel and they might use different language/country settings in their browsers.

[Ry009 · Accepted solution]

For anyone reading, I managed to find a solution that was a good work around for me. I still dont understand why this happened though.

Localtime  = [Your time column here] + TIME(10, 0, 0)

(The 10 adds 10 hours) 

I'll just need to update when Australian Eastern Daylight Savings time kicks in.

[Bipin-Lala · Accepted solution]

Hi @Ry009,

As others have also mentioned, Power BI Service works on UTC time. Hence, using local timezone would display the correct expected time in Power BI Desktop, but once you publish your report on Power BI Service, the servers that host the reports work on UTC time, and hence local timezone would show UTC time when viewing reports online.

Thus, as you already did, you would need to add appropriate hours, like 10 hours in your case to UTC time to display expect time ad per the required timezone.

Although your are on the right path, for handling daylight savings time, you can write a function that can be invoked to convert UTC time to Daylight Savings Time of your required timezone.

The following links would help you with creating the function - 

• https://willpage.dev/2020/06/26/converting-utc-to-new-zealand-time-in-m-power-query/

The function mentioned in the above links works beautifully and helped in coverting UTC times to NZ DST for one of my reports recently.

Just adjust the values for Australia DST and you should be good to go. Let me know if it helps!

[Ry009]

Thanks Bipin

Thank you very much for the guidance relating to daylight savings conversion. Just wanted to query the link you sent me, presumably I replace really only the hours difference (10 in Aus?). Everything else I believe is ok as our daylight savings times both finish and start on a Sunday?

[Bipin-Lala · Accepted solution]

Hi @Ry009,

Yes correct, replace 12 hours with 10 hours for the Australian Timezone. Also, NZ Daylight Savings starts from last Sunday of September, whereas the Aus Daylight Savings kicks in from the first Sunday of October.

Hence, a minor change would be replacing the variable lastSundayOfSeptember with the following -

firstSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 07), Day.Sunday),

 Once the function is implemented, just invoke it for all the columns that you want to shift to the Aus timezone.

[Ry009]

Still showing +11 hours, hopefully something obvious - not sure what I'm doing wrong?

(datetimecolumn as datetime) =>
 
let
//define a variable called date as the date component of the datetimecolumn input
date = DateTime.Date(datetimecolumn),
//define a variable called time as the time component of the datetimecolumn input
time = DateTime.Time(datetimecolumn),
 
//define the day that summer time starts as a variable called lastSundayOfSeptember.
//this is the start of the week in which the 30th of September falls of the year we get from the input
firstSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 07), Day.Sunday),
 
//define the day that summer time ends as a variable called firstSundayOfApril.
//this is the start of the week in which the 7th of April falls of the year we get from the input
firstSundayOfApril = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday),
 
//this gives us a boolean if the input date is in the summer time
isSummerTime =  
        (date = firstSundayOfOctober and time >= #time(2,0,0)) //any time after 2am on the last Sunday in September
        or
        (date > firstSundayOfOctober) //the date is after the last Sunday in September
        or
        (date < firstSundayOfApril)  //the date is before the first Sunday in April
        or
        (date = firstSundayOfApril and time >= #time(3,0,0)), //the date is on the first Sunday in April prior to 3am
 
timeZone = 10 + Number.From(isSummerTime), //define timeZone as an integer that's 12 plus the numerical value of the boolean above
                                           // (1 for summer 0 for not summer) 
 
AEST = 
            DateTime.From(datetimecolumn) + #duration(0, timeZone, 0, 0) //take the original UTC date time and add the number of hours
                                                                         //in timeZone, 12 for winter 13 for summer
in
    AEST

[Bipin-Lala]

Hi @Ry009,

I looked at the function, and that's a great catch actually! Yes, the function is working almost correctly for all the dates, but there's a small logical error that is causing the function to return dates incorrectly for a very specific case (or edge case I must say) and that is when the UTC date is equal to Daylight Savings End date i.e 7th April 2024 

Solution:

Just change the last condition of isSummerTime boolean calculation

date = firstSundayOfApril and time <= #time(3,0,0))

Notice, I have just replaced time >= #time(3,0,0) with time <= #time(3,0,0)

Explanation:

isSummerTime should return TRUE or 1 when DST is on i.e. when UTC_DateTime <= First Sunday of April, before 3 AM. Hence less than equal to the symbol. After 3 AM, DST ends and the function should return FALSE or 0. Hence a small logic error in the original blog.

Elaborated Explanation:

To try this out, I created a sample dataset of UTC Date values, ranging from 4th April to 10th April, with the DST ending on 7th April 2024 at 3AM.

The ADST Original column is created by invoking the function you shared. Notice it has all correct values, but for 7th April 2024, it has wrong converted values. Before 3 AM, it is adding 10 hours, but later, it is adding 11 hours. This indicated that something is unusual with the function.

The ADST New column is created by invoking the duplicate function but modifying the code with the correction mentioned above. Notice that it now outputs the correct values.

Thanks for catching this bug, made me investigate it. I will update the function in my reports as well!

Have a great day!

[Ry009]

You did all the work! Thank you! And the explanation too. Helps a tonne with my learning 🙂

[Ry009]

Thank you so much for taking the time to get back to me and help. I've made the neccessary adjustments. I'm still early in my Power BI learning, but this was a huge step in my development. 

Really means a lot 🙂

[Anonymous]

Hi @Ry009 ,

The issue you mentioned about the local time in your browser reverting to UTC may be related to the time zone settings. Ensure that the time zone settings are consistent across the system, Power BI Desktop, and Power BI Services.
When publishing from Power BI Desktop, you must verify that the time zone settings are correct before publishing.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ry009]

Hi Xianda 

I've checked every setting possible. Could you please confirm every place to check in Power BI in case I've missed anything?

[Brunner_BI]

As far as I know, the Power BI Service always shows UTC time

[Ry009]

Thanks Brunner. 

As I said in my OP, this issue only happened yesterday after creating a schedule in Power BI service. This isn't refresh date of Dashboard itself, it's a table with local time dates, so this should not be happening.

[Brunner_BI]

If you have a NOW() in DAX it will show something else in Power BI Desktop than in the Service after you refreshed the dataset. That is what I meant with UTC in the Power BI Service.

See here: https://community.fabric.microsoft.com/t5/Service/Incorrect-Time-Zone-setting-at-Power-BI-Service/td....