PBI Service refresh setting dates UTC causing local display issues.

[aTChris]

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?

[v-kelly-msft]

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,
Kelly

Did I answer your question? Mark my post as a solution!

[aTChris]

Hi @v-kelly-msft @Greg_Deckler

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.

[v-kelly-msft]

Hi  @aTChris ,

Pls also paste the screeshots of the differences in desktop and Service.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[aTChris]

@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.

[aTChris · Accepted solution]

@v-kelly-msft 

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

[Greg_Deckler]

@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.

[GilbertQ]

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