Power BI reports on CRM having datetime columns coming in UTC

[Surendra_thota]

There  is an Issue with Power BI connection with CRM , the datetime columns are coming in UTC .the workaround currently we implemented is  ( we are in GMT+00:00 Dublin, Edinburgh, Lisbon, London  time zone )

• First created a duplicate of the completion date column
• Then converted it to datetimezone format
• Applied the formula in another custom column: DateTimeZone.SwitchZone([#"Completion_Date - Copy"],+1)

 In this work around we are worried about Day Light savings ? how to solve this issue  , is power BI giving permanent solution for this issue 

There is another workaround where can convert that column toLocal so that it will take based upon your local setting .

• First created a duplicate of the completion date column to perform test
• Then converted it to datetimezone format
• Applied the formula in another custom column: DateTimeZone.ToLocal([#"Completion_Date - Copy"])

Still 2nd workaround is giving same UTC value , to how DateTimeZone.ToLocal  works , is it related to 0365 profile settings ?

[v-ljerr-msft]

Hi @Surendra_thota,

Following is an alternative way to get local time dynamically is by calling an API and use Xml.Document to parse the response(with the correct DST and time zone). Here is the article for your reference.

Here are the steps I figured out:

1. Open your PBIX file and click "Edit Queries"
2. In the query editor, choose the query used to get the local time. under "Home", click "Advanced Editor". In the editor window, replace your query with this one:

let Source = Xml.Document(Web.Contents("http://api.timezonedb.com/v2/get-time-zone?key=LGN45QRJHMMD&format=xml&by=zone&zone=PDT")),

    Value = Source{0}[Value],

    Value1 = Value{12}[Value]

in

Value

    3.  You will need to replace the highlighted key with your own key (to get your own key, you can register a free account from timezonedb.com, and also replace the zone with your time zone, e.g. for Texas/Dallas, you can use CDT for central daylight saving time).

    4. Then update your report to use the result from this query as local time.

---

Regards

[Surendra_thota]

@v-ljerr-msft

Is power BI giving permanent solution for this . we are using these reports across our organization , is it the right solution to use 3rd party api in our reports .

Thanks

Surendra Thota