Forum Discussion

Betsy's avatar
Betsy
Helper IV
10 years ago
Solved

Convert UTC to client time zones?

Hi,   I have date/time information imported from csv files in UTC format. I also have imported time zone information for my various clients. What I'd like to do is create a series of calculated col...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    10 years ago

    Hi Betsy,

     

    We may take a try with the method below:

    Assuming currently we have the Time and UTC column in a table, where [Time] stores the actual UTC time, [UTC] stores the changed hour count between client and UTC.
    Based on the different Time Zone, UTC values changes within -12 to 12 range.


     

    When data loaded into Power BI, open Query Editor, under the Add column tab, click Add Custom Column, then under the formula part, copy and paste the following:
    DateTime.AddZone([Time],0)

     

    Click OK. This step adds the default UTC Time Zone (0 here) under the time stored in [Time], to the newly created ZoneTime column;
    After that, we need to create another column to switch the time zone to the client, also click on the Add custom column, then copy and paste the following formula:
    DateTimeZone.SwitchZone([ZoneTime],[UTC])

     

    After that, we have successfully added the time Zone information into the time. Then we need format the column into date/time/timezone format.
    Select ClientTime column, navigate to Transform tab, choose data type and select Date/Time/TimeZone:

     

    After that, click close and apply.
    Till then we should have finished converting the time into Client local time.


    If any further questions, please feel free to post back.

     

    Regards,

    Charlie Liao

Jay7's avatar
Jay7
Frequent Visitor
8 years ago

I had to display last refreshed time in the report. When I published from my local machine, it had Eastern time zone. However, when it was schedule refreshed in Power BI Server, it displayed UTC time.

 

I modified current time logic as below:

 

DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),10))

 

1. Get UTC time

2. Switch zone to Eastern zone (+10:00)

3. Remove zone from the date time(If I dont do this, it displays UTC time with +10:00. When I changed it's type to DateTime or text, it did not work)

 

You can use "DateTimeZone.From" if you already have the date and time. I haven't tested this though. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    You stated:

    ""You can use "DateTimeZone.From" if you already have the date and time. I haven't tested this though.""

     

    This worked for me, I had the [UTC DateTime] field and I was able to use DateTimeZone.From to convert the UTC (as text) to a new (local) [DateTime] (as DateTimeZone) field.

     

    BUT - if your UTC comes with a TZ (time zone offset), this function does NOT take that into account. It just parses the DateTime and does not use the offset to adjust the result to the local time...

     

    Which I am working on figuring out how to do.

     

     

     

  • WRudd's avatar
    WRudd
    Helper I
    1 year ago

    Hello,

    I watched @HowtoPowerBI Video (https://youtu.be/xOj7KNqe_cI?si=OsOxVOV7LZRtm3p-) which helped me so much! The only thing I had to plug in a number until I got the time to match the central time I needed. See image to see the number in red box I changed which is different from the video. I hoep this helps.