Dates reverting to UTC when published to web from desktop

[CourtneyLeah]

Hi,

Yesterday I published a report from desktop to web. When the data refreshed last night all of the dates reverted from local time to UTC. The dates in the date slicer also changed to the American format.

Is there anything I can do to fix this?

Cheers,

[GilbertQ]

Hi @CourtneyLeah,

When you say you are using dates, how are you getting the dates into your Power BI Model?

By default the dates in the Power BI Service use UTC. So if you are using the built in Dates options you will need to Offset it with the following as I have done in the past. The code below will be modified in the Query Editor.

DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)

In the above my current timezone is 10hours ahead of UTC

[Vincent112358]

Sorry @GilbertQ, but I do not understand your answer.

For example, I have a field Date_Start (dd/mm/aaaa hh:mm:ss) in a table in Power BI Desktop. When I use this field in Desktop, the date is in local french time (UTC +2 in summer or UTC + 1 in winter). But in Power BI Service, the hour is not the local hour but the UTC Hour.

For this field, i do not understand how to use the function DateTimeZone.SwitchZone

Thanks in advance for your answer,

Best Regards

[GilbertQ]

Hi there

The reason is that all the Power BI servers worldwide have their time zone set to UTC. So if you use any features that get the current time of the system it will then be UTC when your data is refreshed on the Power BI service

I do have a blog post explaining how to overcome it

[kionis]

I put in a request for PowerBI to support this natively, please upvote if you agree this would help: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33732571-time-zone-conversion-acc...

[CourtneyLeah]

thanks @GilbertQ,

The dates come from a list in SharePoint, and are in UTC when they are first imported to PowerBI Desktop. I used DateTimeZone.ToLocal in the Query Editor to change it from UTC to local, however it doesnt seem to stick after Ive published to the web.

[GilbertQ · Accepted solution]

Hi @CourtneyLeah,

I also tried that and what I think it does in the Power BI Service, is that all the servers are set to UTC.

So because the server time where the files are hosted is set to UTC it then uses the DateTimeZone.ToLocal which is indeed is UTC.

I would rather suggest using the syntax I provided earlier, as I have tested this and once I have uploaded it to the Power BI Service, it does display the correct time.

[CourtneyLeah]

Does this account for daylight savings time?

[JorgeDiego]

I have been trying to solve the  daylight savings time problem using different datetime and datetimezone functions and I have not fount anyone that helps me. So, I have written the following function to convert 2016 and 2017 datetimes:

if [FECHAUTC] = null then null
else if [FECHAUTC] > #datetime(2017,10,29,3,0,0) then DateTime.AddZone([FECHAUTC], -1)
else if [FECHAUTC] > #datetime(2017,3,26,2,0,0) then DateTime.AddZone([FECHAUTC], -2)
else if [FECHAUTC] > #datetime(2016,10,30,3,0,0) then DateTime.AddZone([FECHAUTC], -1)
else if [FECHAUTC] > #datetime(2016,3,27,2,0,0) then DateTime.AddZone([FECHAUTC], -2)
else DateTime.AddZone([FECHAUTC], -1) as nullable datetimezone

I´m adding -1 and -2 hour to the UTC datetime because I want the correct hour for Spain. You have to change this value in order to calculate the correct time for your country. 

Note that FECHAUTC is a datetime.

It is not the best solution. I think Microsoft should include a regional configuration in Power BI web, but currently ir is solving my problem.

Hope it helps you. 

[GilbertQ]

Good question, I am not 100% sure, but I would like to think so as it is offset by the timezone.

[JorgeDiego]

Hi, I´m having the same problem. 

I´m converting UTC dates to local time, and everything works fine while I´m developing my report in my local machine by using Power BI desktop. But, when I publish the report, the dates shown in power bi web are the UTC ones. 

Is it possible to change the regional configuration in Power BI web? I´m trying to do it, but I´m not finding any option. 

[GilbertQ]

Hi @JorgeDiego

I would suggest trying the proposed solution.

As far as I am aware you cannot change the DateTime settings in the Web settings for the Power BI Service.

[CourtneyLeah]

Sweet,

Thank you