Forum Discussion

jeaninnes's avatar
jeaninnes
Frequent Visitor
9 years ago
Solved

Auto Refresh date conversion issue - Dynamics 365 connectivity

Hi there,  I am really hoping that someone can help with this issue that we are having.  I really like the potential of Power BI - but this particular issue is making it unusable in our organisation...
Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    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.

jeaninnes's avatar
jeaninnes
Frequent Visitor
9 years ago

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.