Auto Refresh date conversion issue - Dynamics 365 connectivity

[jeaninnes]

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!!

[GilbertQ · Accepted solution]

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]

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.

[v-caliao-msft]

@jeaninnes,

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/187966

Regards,

Charlie Liao

[jeaninnes]

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. 

[GilbertQ]

Hi @jeaninnes

I think that would be the best way, something simple so that you can see if it is working as expected and then take it from there.