Forum Discussion
Time/Date Problem when publishing to Power BI Service
I think the issue there is that the data isn't being refreshed. I publish the data, then it refreshes and then the dates change
I've checked the region settings on the Desktop version and it's in set to UK. I can only see the option to change the region in the schedule refresh section, and I set that to UK too
Since the file is still connected to your Dynamic 365 - I can't refresh it or update the queries.
Back to your original question - when you copied the date/time columns in your query - you said that in that moment - the columns changed to a different date?
In the sample you shared - those dates are already different - even before the file is published. I can't really change that - but this is where I want to zero in on.
These dates should be the same - especially in Power BI Desktop.
Unfortunately - I can't toggle with your original query because of the Dynamic connection - and when I make a copy of it - I am not able to 'replicate' this date change - that happens.
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.
hi michael_knight - Same issue with me as well - the desktop numbers and published numbers match but unable to replicate the refresh scenario due to credentials issue.
As far as I am aware there is no inbuilt process of rounding off dates in Power BI.
Also the conversion of date time to date - did you do that in Power Query? Thanks!
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.
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?