Forum Discussion
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 columns where date/time is reported in client time. So I could look at responses on Monday mornings vs. Friday mornings, for example.
How would I do this?
Thanks for the help!
Betsy
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
37 Replies
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
v-caliao-msft / Charlie Liao
Is it planned to provide an implementation by Microsoft for
Power BI service to evaluate the browser time zone settings?
Best would be:
- Get the current time zone setting of the connected browser
- Allow (via dedicated datetime methods) filtering and display to use that request specific user time zone
= Users from Australia see the same reports as those from Europa as those from the USA.
But: Those from Australia with the appropriate Australian time zone, those from Europe ...Example:
The very same record would show 01:40 May 1, 2018 in Australia
But: 17:40 April 30, 2108 in SwitzerlandAnd: 11:40 April 30, 2018 in New York
Regards, David
I have followed this process successfully at the query level. However, when referencing the ClientTime in a report, the time zone information is stripped out and the fields revert to Date/Time from the Date/Time/Timezone format; times revert to UTC.
How can this data be used in a report? Is this a known issue with Power BI?
Having a similar issue, when I'm in the Query Editor and convert the column type to "Date/Time/Timezone" it shows the proper -05:00. However on the actual report as a Date/Time it shows UTC.
Is it just me, or is this an insane amount of work to do something that should be out of the box? Power BI seems to have suggestions that are WAY over engineered for something that Tableau can do without even thinking.
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.
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.
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.
Assumption:
You have a datetime column that YOU know is in UTC ( but probably power bi does not know that)
If you have a column in UTC, tell power bi so by creating a new colum called "utc_time" based on your prior utc column, use this code: DateTime.AddZone([ here put the name of your utc column ], 0 )
Now you will notice some zeros:
Change the data type to "date time zone":
Now select the new column, go to Add Column> Time and click on "Local Time":
This is the magic column that is useful for your reports, just change the name of the column (if you like) :
Warning that Power Query running on your local computer will convert this to YOUR local time, HOWEVER - if you intend to refresh your dataset in the cloud - it will be running on the server, which uses UTC as it's local time. For me, the LocalTime feature is pretty useless. Maybe if you are just conducting the analysis one time, not trying to make a continuously updating report?
Hi
I am building one report, report is embedded into application.
My requirement is report need to change dynamically as per the logged user time zone.
In application we have provision of changing time zones of user.
All power Bi services activities handled from India and users are the part of other countries time zones are EST,CST,HST and AKST
Is it achievable please suggest any solution.
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 hope this helps. I do all configurations in Power BI desktop, but pbix and data sources are stored in the cloud (OneDrive), so I haven't had any issues with refresh schedule.
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 hope this helps.
