timezone issue

[Harish85]

Hi, I am facing time zone issue, there is column Changed at CRM ,I should convert it to CST time OR UTC , what is the process.

Regards,

Harish.

[Anonymous · Accepted solution]

Hi @Harish85 ,

Thanks Uzi2019  for the quick reply and solution. I have some other ideas to add:

Add a Custom Column, if your original datetime is in local time, you need to convert it to UTC first. Use the following formula in the custom column:

DateTimeZone.SwitchZone(DateTimeZone.From([CHANGED_AT_CRM]), 0)

This converts the datetime to UTC by setting the timezone offset to 0.

To convert the UTC datetime to CST, add another custom column with the following formula:

DateTimeZone.SwitchZone([UTC], -6)

This adjusts the timezone to CST, which is UTC-6 hours.

If you want to remove the timezone information and keep only the datetime, you can use:

DateTimeZone.RemoveZone([Custom])

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Uzi2019]

Hi @Harish85 

You can use DateTime.AddZone function to achieve your requirement. This function adds the timezonehours as an offset to the input datetime value and returns a new datetimezone value.

DateTime.AddZone([CreatedOn],3)

https://msdn.microsoft.com/en-us/library/mt253514.aspx

Create a new column and add 'Zone' to your Date Time column e.g. DateTimeZone = DateTime.AddZone([Created],0)

2. Create a new column and add 'Local' to your new 'DateTimeZone' column e.g LocalDateTime = DateTimeZone.ToLocal([DateTimeZone])

3. Create a new column to remove the 'Zone' from your new 'LocalDateTime' column e.g. CleanLocalDateTime = DateTimeZone.RemoveZone([LocalDateTime])

Or 

time with zone = UTCNOW() - time(6,0,0)

Check these blogs

https://community.fabric.microsoft.com/t5/Desktop/Convert-utc-to-local-time-zone-using-Power-Query/m...

Convert Time Zones in Power BI using DAX – NateChamberlain.com

Solving DAX Time Zone Issue in Power BI - RADACAD

Handling Different Time Zones in Power BI / Power Query — The Power User

I hope I answered your question!

[Harish85]

I am trying to convert to UTC time zone but unable to do it, getting below error, Please help me.