Forum Discussion
PBI Service refresh setting dates UTC causing local display issues.
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
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.
Kelly
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!v-kelly-msft I will try to create the example today but here are screen shots of the issue.
This is the table of contract lines. Note rows 1 & 2 show MonthDate (type Date) with pro rated values of 1. That means its a full month, the measure takes the revenue and multiplies by the pro rated amount. There are no rows for Month Sept.
Here is the report which has been refreshed by the service. Note the revenue being reported in Sept for product 3.01 & 3.02
I refreshed the dataset locally on PBI desktop and published the report, note the report correctly shows 0 revenue for Sept. As soon as it completes the refresh process after publish it reverts to reporting an hours revenue in Sept.
Ive also checked queries. The col MonthDate is created by the custom function initially as text. I convert it to a date. Its never been a formatted datetime.
I think the issue is coming from the Calendar table. Its a generated list of dates and correpsonding cols. Although again the cols are always just dates so timezone should'nt be a factor however im assuming because its being created by the service some localisation is being applied.
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