Forum Discussion
Time/Date Problem when publishing to Power BI Service
Hi swise001 and Anonymous ,
I import the data from Dynamics 365 and include the date table that I use for all my reports. I change the Data types with the dates in from Date/Time/Timezone to Date. The reason for this is because the Data Types for the date in the Data table and the Date table need to be the same if I want to create a relationship between the two
After I've done that, I create the formula, and Include the USERELATIONSHIP function in order for the Dates in the Data table to use the Dates in the Date Table in a visual
I just made a sample dataset of what I'm working with, with a link to what it looks like published. I also duplicated the Date columns I'm working with so you can see what it looks like before and after I make the changes. As you can see the figures are different in both the PBIX file and the Published version. The figured in the PBIX file are what I want.
https://www.dropbox.com/s/7h70duzrrnb2ksf/comp%20test.pbix?dl=0
Cheers,
Mike
When I publish your PBIX file to my PBI instance - the numbers in the desktop version and the number in the service - are the same:
Desktop
Service
I can't duplicate the changes that take place in your shared version of the published report.
Are you saying that when you go through this exact same process - and publish this report - the values showing in the service are changing?
Could this be related to regional settings in the file - and how that is handled as it moves into the Cloud. I don't have a direct answer - but wanted to share my findings.
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.
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 - 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.
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
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?