Forum Discussion
Auto Refresh date conversion issue - Dynamics 365 connectivity
Hi jeaninnes
What I can confirm from the Microsoft Power BI Team is that all the Power BI Servers Date/Time is stored as UTC.
I am also based in Australia (Brisbane) which is GMT + 10 so what happens is that when you view your dates locally it will be based on GMT + 10.
But when you upload it to the Power BI Service (Any data centre including Australia) the dates will be in UTC.
So what I have done with my datasets is to offeset the Date/Time by 10 hours, so that when it uploads to the Power BI Service it takes this into account and displays correctly. Which in your example will make it show as 01 Oct.
I would then create a new custom column in the Query Editor as follows below where the name of my existing column is called: SalesDateTime
=[SalesDateTime] + #duration(0,10,0,0)
That will then add 10 hours to your Date/Time.
Hi jeaninnes
What I can confirm from the Microsoft Power BI Team is that all the Power BI Servers Date/Time is stored as UTC.
I am also based in Australia (Brisbane) which is GMT + 10 so what happens is that when you view your dates locally it will be based on GMT + 10.
But when you upload it to the Power BI Service (Any data centre including Australia) the dates will be in UTC.
So what I have done with my datasets is to offeset the Date/Time by 10 hours, so that when it uploads to the Power BI Service it takes this into account and displays correctly. Which in your example will make it show as 01 Oct.
I would then create a new custom column in the Query Editor as follows below where the name of my existing column is called: SalesDateTime
=[SalesDateTime] + #duration(0,10,0,0)
That will then add 10 hours to your Date/Time.
Thanks for the prompt reply. From my research, I thought this was one of the workarounds available. I haven't been able to get it to work so far (!). I created a new column and added 10 hours to the date. I've then based my charts on the adjusted date. I'm still getting issues.
I'm going to start one of our reports from scratch, start with something really simple and try it again.
As guavaq said, it display UTC time in Power BI services. You coudl convert it to you local time. Create a custom column by using the expression below.
=DateTimeZone.SwitchZone([YourcolumnName],YourLocalTimeZone)Please refer to the link below to see my reply.
https://community.powerbi.com/t5/Desktop/UTC-to-AEST/m-p/187966Regards,
Charlie Liao
Thanks for the responses. I can confirm that the solution offered worked for me.
I started with a DateTimeZone column. I kept this and called it RevRecDate_UTC. I then added a new custom column and added the duration #duration(0, 10, 0, 0) to the original column as advised. I then changed the type of my new column to DateTimeZone and renamed to RevRecDate_AEST. I then use the AEST to report on and display within my graphs etc.
I can confirm that the data is the reporting the same in both the desktop & service versions which is what I was after (woohoo!!). I haven't verified the changing of the zones as suggested in the second solution.