TimeZone functions in PowerBI desktop do not work in the Service

[dscott73]

Created a report in Desktop.  The date column being brought in is in UTC +0.  Using that as a base column the following additional columns are created:

= Table.AddColumn(#"Renamed Columns1", "Created Date UTC", each DateTime.AddZone([Created Date],0))

= Table.AddColumn(#"Reordered Columns", "Created Date UTC - 7", each DateTimeZone.SwitchZone([#"Created Date UTC"], -7))

Change Types Applied:

= Table.TransformColumnTypes(#"Reordered Columns1",{{"Created Date UTC", type datetimezone}, {"Created Date UTC - 7", type datetimezone}})

= Table.TransformColumnTypes(#"Changed Type",{{"Created Date UTC - 7", type date}})

Publish Report, and the following occurs:

L/H Side is Desktop.  R/H side is report published to Service.

[dscott73]

I guess the insert picture did not work..

[v-qiuyu-msft]

Hi @dscott73, 

The [Created Date] should be a datetime type column. From your screenshots, I don't find the time part value, would you please provide the exact [Created Date] values? 

Best Regards,
Qiuyun Yu

[dscott73]

Hi @v-qiuyu-msft , below are some sample dates/times.

The come in as DateTime datatypes.  I do the abovementiond transformations on the "Created Date" column, creating a UTC column, and then a Created Date UTC - 7 column.  This works fine in the desk top.  Publish to service, appears to work fine, refresh 4-5 times, and then it swaps back.  What is odd is, I create a test report on the Power BI service from the Dataset. The columns are incorrect of course.  I download it, open up in Desktop mode, still incorrect.  Go to Transform, all the steps are there, they just dont do what they are supposed to.

[v-qiuyu-msft]

Hi @dscott73, 

I tested on my side, the result in Power BI desktop and service is the same. Please test our sample report on your side to see if I miss any steps. 

Best Regards,
Qiuyun Yu

[dscott73]

@v-qiuyu-msft Now refresh the dataset and see what happens.  I will check out your report and let you know.

It works at first, and then after refreshing a few times, it goes back to the UTC/Original time/date in the report on the service.

[dscott73]

@v-qiuyu-msft I published your report to the service, and the same thing happened.

[v-qiuyu-msft]

Hi @dscott73, 

It looks like my report works fine on your side, right? 

Is it possible for you to share your pbix file? If it is, please remove sensitive data in the report, upload pbix file to your OneDrive for Business then paste the share link here. 

Best Regards,
Qiuyun Yu

[dscott73]

@v-qiuyu-msft , Did you have the time portion on your dates?  Here is the original screen shot.  "Created Date UTC" has the time portion on it.  "Createdt Date UTC -7" is just formated/shown as a Date.   Created Date UTC of 10/1/2019 2:10:15 AM should be Created Date UTC - 7 as 9/30/2019

The top grid is how it appears in desktop.  Bottom is how it appears in service.

If it helps, the data is coming from D365 staging tables.  The Created Date is recorded at UTC +0

These are the following Applied Steps in Power Query:

= Table.AddColumn(#"Renamed Columns1", "Created Date UTC", each DateTime.AddZone([Created Date],0))

= Table.AddColumn(#"Reordered Columns", "Created Date UTC - 7", each DateTimeZone.SwitchZone([#"Created Date UTC"], -7))

= Table.TransformColumnTypes(#"Reordered Columns1",{{"Created Date UTC", type datetimezone}, {"Created Date UTC - 7", type datetimezone}})