Forum Discussion

Tlotly's avatar
Tlotly
Helper V
3 years ago
Solved

Hours on a visual changing when refreshing on Report server

Good day   I have a report with a visual which shows views per hour.  The report works perfectly on desktop version and when I save/publish it on the Report Server:   Desktop version: The ...
REFRESH REPORT
  • d_gosbell's avatar
    d_gosbell
    3 years ago

    Issues like this are often caused by having code in Power Query that is dependant on the local timezone of the machine running the code and then having the server set with a different timezone to your client machine. So you may want to check if you have any calls doing anything like DateTimeZone.ToLocal and/or check the timezone setting on the server machine.

  • Tlotly's avatar
    Tlotly
    3 years ago

    Hi d_gosbell 

    Update:

    Thank you for the response. It helped a lot as I managed to finally resolve the problem.

     

    Firstly, I changed the format of my date column ( [TimeStart]) on Power Query to datetime. I then created a custom column using my date column i.e. DateTime.ToText([TimeStart],"dd-MM-yyyy HH:mm:ss"). Lastly, I used the custom column to create the Hours column, and it works perfectly.

     

    This is how it's showing now after scheduled refresh on Report Server:

     

    Thanks!

d_gosbell's avatar
d_gosbell
Super User
3 years ago

Issues like this are often caused by having code in Power Query that is dependant on the local timezone of the machine running the code and then having the server set with a different timezone to your client machine. So you may want to check if you have any calls doing anything like DateTimeZone.ToLocal and/or check the timezone setting on the server machine.

  • Tlotly's avatar
    Tlotly
    Helper V
    3 years ago

    Thank you for the response. 

    I'm not sure if I checked the timezones properly, but I formatted the date to datetimezone. Then created the hours that I'm using on the visual from the same datetimezone date. 

    Dax used to create the hour column:

    Hours1 = FORMAT([TimeStart - hours - Copy.2],"HH AM/PM")
     
    Date column:

     

    I also checked the format on my machine as well as on the server, it's using the timezone below:

     

    It's still not working after the server refresh, it goes back to showing only 12 hours.

     

    Thank you

    Matlotlo

     

    • Tlotly's avatar
      Tlotly
      Helper V
      3 years ago

      Hi d_gosbell 

      Update:

      Thank you for the response. It helped a lot as I managed to finally resolve the problem.

       

      Firstly, I changed the format of my date column ( [TimeStart]) on Power Query to datetime. I then created a custom column using my date column i.e. DateTime.ToText([TimeStart],"dd-MM-yyyy HH:mm:ss"). Lastly, I used the custom column to create the Hours column, and it works perfectly.

       

      This is how it's showing now after scheduled refresh on Report Server:

       

      Thanks!