Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Tlotly
Resolver II
Resolver II

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:

Tlotly_0-1669789561916.png

The report is scheduled to refreshed every hour on Report Server. So as soon as the scheduled refresh runs, it throws out the other hours, and they will only end on 12pm:

 

Scheduled refresh:

Tlotly_1-1669789708512.png

 

What causes this and how do can I fix it?

 

Thank you in advance.

Matlotlo

2 ACCEPTED SOLUTIONS
d_gosbell
Super User
Super User

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.

View solution in original post

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:

Tlotly_0-1670829667508.png

 

Thanks!

View solution in original post

3 REPLIES 3
d_gosbell
Super User
Super User

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.

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:
Tlotly_2-1670308630822.png

 

I also checked the format on my machine as well as on the server, it's using the timezone below:
Tlotly_0-1670308467596.png

 

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

 

Thank you

Matlotlo

 

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:

Tlotly_0-1670829667508.png

 

Thanks!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.