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.

 

Some details about us:  We are Australian based business, recently implemented Dynamics 365.  We have started using Power BI as a way to provide more detailed reports than Advanced Finds can give us.  

 

We have started with the desktop version of Power BI and created a few reports that we are quite happy with.  These allow us to forecast our income etc.  However, these do take a reasonable amount of time to refresh, so we would ideally like our users to be able to host them on the Power BI service & make use of the automatic refresh.  When I publish the reports to the online service, however, lots of my dates go out of whack.  I have tried to research this issue & tried a few work arounds but nothing I have tried is working.

 

Basic issue:  A date in Dynamics 365 is 1/10/17 (1st October 2017).  In my desktop Power BI report, this appears correctly.  However in my power BI online report, this date is being pushed to 30th Sept 2017.  

 

Any help with this would be most appreciated!!

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.

5 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    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 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.