Forum Discussion
PBI Service refresh setting dates UTC causing local display issues.
Hi, I have a report which runs perfectly on the desktop. The issue is when the data is refreshed in the service.
I have a table containing contract data. I report revenue per month over the term of the contract. I have a row for every month of between the start and end dates with a field that defines the month using the first date of that month. I have a relationship with a calendar table, [Contract]Month -> [Calender]Date (*:1) single
I display the data in a matrix. Rows = Customers, Cols = Months, Values = Revenue Measure (sum(rev)). The report is perfect locally. When I upload this to the service and it refreshes the report in the Power BI client is showing revenue in the month prior to the data. My time zone is GMT, im assuming its because the service is UTC. However, all fields are formatted as Dates. I can only assume the datetime with timezone is being passed. e.g. 01/10/2020 00:00:00 UTC which then displayed as 31/09/2020 23:00:00 GMT.
I have seen lots of comments about using the NOW() DAX function and having to define the timezone but I don't see that being the issue in my case. I could set the month data on the contract table to MthYear but I don't want to have a many to many relationship.
Can anyone think of a solution or why the service is changing date to datetime timezone?
I've resolved the issue.
The API is presenting information in UTC so the error in the report was not visible in the desktop because I was removing the localization. It was only when I added the start and end dates back into the report so I could see what the service created I was able to notice a change in start date compared to my local report.
I changed everything to date timezone and its consistent across all platforms.
Thanks
7 Replies
aTChris - First thing I would do is reset the report to default settings as perhaps there is a user filter on it that is causing it to act weird. Last I checked, time zones don't generally throw things off by a month. A day maybe, but not an entire month.
- Hi there
Yes all the Power BI Servers are set to UTC.
What I suggest doing is always offsetting your columns with your timezone if you are relying on any DAX or Power Query function that gets the local time.
Depending on where you are setting the dateTime column will depend on where you modify it to use the valid dateTime.
If in Power Query you can use the #duration(day,hour,minute,second)
In my timezone I would put DateTimeColumnName + #duration(0,10,0,0) for GMT +10 Hi aTChris ,
I aggree with what suggested by Greg_Deckler ,it is strange that you lose data for a month,better check whether there's some filters on your data,then try to download your file to local .pbix,and check whether in the .pbix,the data has been updated.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!I'm not losing a month. The date 01/10/2020 is being offset by an hour (daylight saving) so one hour's worth of revenue is being reported in Sept. Although the column is only a date so timezone or daylight saving should not be a factor.
GilbertQ Im not getting the local time in that query. The report does use it in others but again not in this table. I simply get the Contract Start and End Dates, 01/20/2020 to 31/09/2021 for example and the monthly value is £10k. Using a function I create 12 rows, one for each month. The reason for the function is to calculate pro-rated amounts if the contract starts or ends mid-month. In this case, it's not a factor. I then display those 12 rows of revenue against the product it's contracted to.
I cant share the pbix file due to sensitive information but I will try to create an example.
Hi aTChris ,
Pls also paste the screeshots of the differences in desktop and Service.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
