Forum Discussion
Time/Date Problem when publishing to Power BI Service
Yeah, that's annoying that you're unable to connect
Incorrect. I have a date column for Completion, Expected Completion and Fall Through. All three of them have the Date/Time/Timezone. I copied it in the rawest format for comparison purposes to help yourself and others.
Normally I change the data type from Date/Time/Timezone to just a date Date Type, which is what I did so I can present the problem
I noticed that about the dates being different, I found it odd. Is there any automatic function within PBI that rounds dates?
Yeah I believe that Date/Time/TimeZone data type is where the issue is being introduced.
I think you'll need to switch those columns to your current timezone using
https://docs.microsoft.com/en-us/powerquery-m/datetimezone-switchzone
Found this article about it as well.
https://www.poweredsolutions.co/2019/10/21/handling-different-time-zones-in-power-bi-power-query/
Since the times are 11:00PM (23:00) - if the service kicks the timezone part of the columns 1 hour forward - it will change the day. I believe that's what is happening.
Try using the timezone switch formula in power query as part of your column copy step. That may what is needed.
hi michael_knight - Also may be you can try to move the formatting of the date into calculated columns in the Power BI data model. You would need to test it with the refresh and see the behavior but this seems to calculate as expected as seen in the below screen shot.
New_Comp_date_Copy = DATE( YEAR(new_offer[new_completionsdate]), MONTH(new_offer[new_completionsdate]), DAY(new_offer[new_completionsdate]) )New_ExpComp_date_Copy = DATE( YEAR(new_offer[new_expectedcompletionsdate]), MONTH(new_offer[new_expectedcompletionsdate]), DAY(new_offer[new_expectedcompletionsdate]) )Else the option mentioned by swise001 would be the best way forward.