Desktop refreshes correctly show the converted local time dates but the Service reverts back to UTC?

[D_PBI]

Hi all,

I've noticed an odd behaviour that I require help on please.

I have a report that uses an OData Feed connector to extract from a Dynamics 365 solution (D365). The D365 solution is set, by default, to the UTC timezone. My Power BI Desktop's Regional Settings are set to 'English (United Kingdom)'. As a result when I convert a UTC timezone to a Date type it gets converted to the current BST/GMT date.

All is well when I refresh the report using Desktop but when I refresh the report within the Service the dates values are reverted back to UTC - this isn't the expected or desired outcome.

I have some screenshots to help explain. Please bear in my the screenshots below are from a PBI Service downloaded report (it'll make sense as you go through the screenshots).
When I extract the data in Power Query (PQ), the very first applied step shows the Received Date as the UTC timezone. This is expected.

By the end of the applied steps, the Received Date shows the BST Date. This is because one of the applied steps is to convert the UTC timezone to the Date type. As my Regional Settings are UK it converts it accordingly.


When I click 'Close & Apply' from PQ to the PBI side, remember - I haven't actually refreshed any data, I find the PBI side is showing the UTC timezone value. This is expected as I didn't explcity refresh the PQ data and then move it into the PBI side. The value you are seeing here is due to the report being downloaded from the PBI Service.


Just to confirm what I'm seeing in a table visual, I present the below:

At this point, the PQ applied steps are correctly showing the UK Date value but the PBI table is still showing the UTC timezone value in a Date type - as expected being a PBI Service refreshed downloaded copy of the report.

If the above is confusing, sorry, this should clearly explain my issue.

I take the opportunity to refresh the data within the Desktop and check the value held in PBI. It now shows the Date in the correct UK value. So clearly, the report works when having its data refreshed within Desktop.


I then publish the same report to the Power BI Service and take screenshot of the value in the table visual. As you can see it is correctly showing the Received Date in the correct Date value. This is before any PBI Service refreshes take place.


I then refresh the report from the PBI Service and the value is once again reverted back the UTC timezone date..


So it would seem that when I refresh (and publish) from PBI Desktop the Received Date is converted to the UK format correclty (i.e. showing the 01 April 2021).
As soon as a PBI Service refresh is completed the Received Date is showing the date part of the UTC timezone value (i.e. 31 March 2021).

I've tried clearly Data Connections, clearing cache, using two different user accounts to test all the above steps, etc... but all outcomes are the same. I should point out too that my web browser, that I open PBI Service in, is set to the United Kingdom language.
This isn't just happening on one record. This is happening for all the records which have a Recieved Date being the last day of the month at 23:00:00 hours timestamp.

Why is the data refresh from the PBI Service saving the UTC date value?  Is it something to do with the configuration of the PBI Service within our organisation?  Or do I need to handle the UTC timzeone to local date differently?

Any help or guidance will be much appreaciated.
Thanks.

[GilbertQ]

Hi @D_PBI 

Awesome thanks for letting us know.

[GilbertQ · Accepted solution]

Hi @D_PBI 

This is because all the Power BI Servers are in UTC

Here are more details and how to solve the issue: https://www.fourmoo.com/2017/10/03/power-bi-did-you-know-all-power-bi-services-servers-are-in-utc-no...

[D_PBI]

@GilbertQ - thank you for your response. It has pointed me in the right direction.
Considering Daylight Saving is set at different dates each year I would've had to update, in some manner, the dates yearly. Many of the reports I've inherited do not use a Date table so I would've had to do a lot of configuring. I've chosen to handle the date conversion using Views. I've tested the Views and can confirm all converts accordingly whether on the Desktop or in the Service.