Forum Discussion

michael_knight's avatar
michael_knight
Icon for Post Prodigy rankPost Prodigy
5 years ago

Time/Date Problem when publishing to Power BI Service

Hi,   I've got a problem with my Date/Time when publishing to PBI Service   This dataset I've got is used to show the completions in the business. I have a Completion Date and I converted it from...
Need Help
Tips and Tricks
swise001's avatar
swise001
Icon for Continued Contributor rankContinued Contributor
5 years ago

michael_knight 

 

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. 

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.