UTC CRM Timestamp Issues with Q&A

[BPhetchamphone]

I am currently getting data from CRM Dynamics using an OData connection. Everything is great with the exception of one unfortunate issue ; the UTC timestamp. All of the data in CRM Dynamics is stored in UTC, and though you may be able to format the data to local time in power bi desktop, whenever you use Q&A on this dataset (i.e. "total sales today") the "Today" is also in UTC time. This renders Q&A completely useles for anything that is time-based. Has anyone found a solution to this? How do you deal with the UTC timestamp in CRM ? 

[Phil_Seamark]

If you are using Power BI Desktop (PBIX files) you should be able to create a calculated column in your Power BI model that performs a date-offset from the UTC column which you can then use in Q & A

[BPhetchamphone]

@Phil_Seamark. This works for the most part, however, around 5pm MST the card for  "Sales Today" goes blank. It repopulates the next morning with proper data, but as soon as it's midnight UTC the card becomes blank. Any thoughts on workarounds ? 

[Phil_Seamark]

Sounds like an issue with your formula.  What timezone are you in?

[BPhetchamphone]

@Phil_Seamark I'm in Mountain Time. I'm subtracting 7 hours from the UTC timestamp. 

[Surendra_thota]

How to handle Daylight saving Hour difference

[BPhetchamphone]

@Phil_Seamark I'm in Mountain Time. I'm subtracting 7 hours from the UTC timestamp.