Power BI Report server change my date fields

[jguerrero]

Hi, guys, I have a problem with Power BI Report Server (PBIRS).

I have a DATE column in my PBIX that is changed when it is refreshed in PBIRS. It is a DATE field, not calculated. Comes directly from my table in MySql.

Working locally using Power BI Desktop for Report Server (PBIDRS), the dates look normal. Let's say that I refresh my PBIX and none of the dates in my column are greater than the time I am running it.

If I publish the dashboard in Web Services, dates look normal, even if I refresh the dashboard.

But, if I publish the dashboard in PBIRS, while it is not refreshed, the dates are normal, but when it is refreshed by schedule, the dates jump 5 hours ahead.

If we were talking about a calculated refresh date, I know I will receive a UTC date. That is the server’s way.

But why MY data? Is like I am using a sales amount and the server is modifying MY TABLE data adding a fixed amount to it.

Any idea on why this is happening?

[jguerrero]

I found an answer in the second link you sent me.

(https://community.powerbi.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-with-Daylight-sa...)

Still it looks pretty odd to me that table content is modified by the server, but at least my problem is now solved as in the link attached.

Thanl you d_gosbell. Appreciate it.

[jguerrero]

Thank you d_gosbell.

The DW and PBIRS server is the same a it is in UTC - 5 US.

Let me check the extraction propcess in all the related tables. Maybe one of them is doing a format or time zone change that is affecting the entire process. 

Will post an update here after that.

Regards.

[d_gosbell · Accepted solution]

The usual reason for dates shifting by x number of hours is a difference in the timezone settings between your PC and the server running PBIRS and you have some step in your query converting a timestamp from UTC to "local time". My guess would be that you are probably in EDT (UTC-5) and the PBIRS server has been set at UTC time.

So when you upload the data it has been adjusted to UTC-5 clicking the refresh button in PBIRS does not re-load the data it merely causes the visuals to re-query the existing data model. It is only when a scheduled refresh happens that the data is read from the source system and any transformations in the queries are applied. If you do have a step converting the times to a "local time" when this runs on the server it will calculate using the timezone setting on the server and this is different you get a different local time. 

The simple fix is to use something like DateTimeZone.SwitchZone, but that does not account for daylight savings. (see here https://blog.crossjoin.co.uk/2017/03/28/daylight-saving-time-and-time-zones-in-m/ for a description of  this issue). 

If you are just converting to your own timezone you may be able to build your own function using your local daylight savings rules (see https://community.powerbi.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-with-Daylight-sa... )

The problem with daylight savings that governments can (and do) change the rules whenever they like, making it hard to code them into a system