Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Surendra_thota
Helper II
Helper II

Power BI reports on CRM having datetime columns coming in UTC

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 ?

2 REPLIES 2
v-ljerr-msft
Employee
Employee

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. Smiley Happy

 

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

@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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors