Forum Discussion

jguerrero's avatar
jguerrero
Icon for Helper II rankHelper II
4 years ago
Solved

Power BI Report server change my date fields

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 f...
  • d_gosbell's avatar
    4 years ago

    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-savings/td-p/789919 )

     

    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