Changing time From UTC to Local Time

[Anonymous]

Hello all,

I tried multiple functions to change the time from UTC to my local time, but its not working 

as you can see above in colomnm [EVENT_TIME_UTC], I have the time in UTC and my local time is UTC+3 (Saudi Arabia)

The functions I tried have worked, but it doesnt give me changed time, it only adds +3 in the end.

I need a funtion that changes the time itself.

Thank you.

[PhilipTreacy · Accepted solution]

HI @Anonymous 

Download this sample PBIX file with the following data and measure

Create a Calculated Column using this

EVENT_TIME_LOCAL = 'Table'[EVENT_TIME_UTC] + TIME(3,0,0)

Giving this result

Regards

Phil

[negi007]

@Anonymous You can do it in the powerquery window, open your data in the powerquery window and then add a custom column

use the below code in the custom colum

= Table.AddColumn(#"Changed Type", "Custom", each DateTime.AddZone([Time in UTC],2,30))

then change the data type of the new column to Date/Time like below

you are done. let me know if you face any trouble

[themistoklis]

Hello @Anonymous 

Are you trying to do the datetime transformation using Power Query or DAX?

Have you tried using the DateTime.AddZone and DateTimeZone.SwitchZone functions in Power Query?

https://docs.microsoft.com/en-us/powerquery-m/datetime-addzone

https://docs.microsoft.com/en-us/powerquery-m/datetimezone-switchzone

https://www.poweredsolutions.co/2019/10/21/handling-different-time-zones-in-power-bi-power-query/

Example in Power Query: 

DateTimeZone.ToLocal(DateTime.AddZone([date_created_UTC],3))