Allow formatting datetimezone fields to user local timezone
Typically date & time information is available in UTC (if not then put it to UTC at source), you just want it rendered in the users' locale/time zone at reporting time.
Adding an option to format datetimezone fields in the users' locale/time zone (based on the user account settings) would probably solve 90+% of requirements for rendering time zone information.
Currently MS does not provide for showing offset information (or local time zone) as you only have date/time format options, but in the query editor you can set type to datetimezone which show offset info so it's clearly available - just the rendering that lacks.
Not having this functionality is forcing people to resort to hacks like using DateTimeZone.AddZone() with hardcoded time offsets - not scalable where DST is involved. Current workarounds obviously only work as long as the viewer is in the designated time zone and does not scale across a global audience.
Christiaan B.
shared this idea
29 comments
-
Raymond Boone
commented
Very surprised this still isn't built into Power BI or Power Query.
-
Anonymous
commented
This is something that pretty much exists everywhere apart from Power BI!
Just choosing which timezone is local is what we indeed need. Either choosing a report wide time zone, OR being able to specify in the DAX query which time zone I want this date to switch to. For example, from UTC datetime to UK datetime. -
Anonymous
commented
Truly absurd that this is still an issue.
-
Anonymous
commented
Desperately need this. Reporting is unreliable without easy way to show in local time
-
Ravi
commented
Currently threre is no option to show the data as per the user local timezone in power BI Service. if a country is having multiple timezones , can we implement something that we are using in D365 ( which will show the data according to the users local timezone). Or is there any idea already exists. i wiuld be helpful if we have a option to configure timezone in Power BI service.
Thanks. -
Donovan Regel
commented
Come on product team - this is a basic necessity! Just need a formatting option on datetimeoffset fields that allow you to render it in local/alternative timezone.
-
Michael
commented
So much needed, especially as not all countries flip to DST on the same date in the year.
E.g. we receive data from a server located in Seattle. So the timezone of the data is Pacific Standard Time (PST = GMT -8) until 10 Mar 2019. Then it switches to Pacific Daylight Time (PDT = GMT-7) until 3 Nov 2019, to then switch back to PST.
To make it worse, e.g. users in the UK switch to British Summer time (BST = GMT+1) on 31-Mar-2019 and flip back on 27-Oct-2019 (GMT+0). So most of the time we are 8 hours apart, but late March / early April and late October / early November it gets very tricky.
And if you think this is not complex enough, Europe is already discussing to permanently scrap DST in the EU soon. So even if you build your own query logic with hard coded dates when DST starts/stops by country, you can rework this logic again in every model when things change.Thus offering a solution to 'select a timezone' will only work if combined with selecting whether 'DST is enabled or not' in that timezone. Also, when converting from one timezone to another, the DST flag must be available for both the source and the target value separately.
So I like the idea of Eric Thomas for a simple text string conversion, but would add another (optional) DST parameter.
##############
How about an option to SELECT THE TIMEZONE and DST ENABLES when converting a UTC Text Column to DATE-TIME?- Right click column
- Select Date-Time
- Choose Timezone (i.e. for GMT-8 for Pacific Time, so always use the 'standard' time)
- Choose DST Enabled (True/False, this shall adjust GMT offset by -1/0/+1 based on current date)
- Click Apply
- Time is converted correctly!
#################And to make it really generic, I suggest to add an optional DST parameter to all the DateTimeZone() functions. Or even add a new set of DateTimeZoneDST() to the M language with the extra DST flag. In fact there must be two DST flags added, one for DST source, and one for DST target, i.e. is DST enabled for local system time, as the 'culture' parameter won't know this.
-
Anonymous commented
YES, PLEASE! How is it that power BI is not able to render results in the user's local time zone, given that nearly every we platform on earth stores data in UTC and presents it in the user's local time zone?
-
Eric Thomas
commented
How about an option to SELECT THE TIMEZONE when converting a UTC Text Column to DATE-TIME?
- Right click column
- Select Date-Time
- Choose Timezone
- Click Apply
- Time is converted correctly! -
Dan Szepesi
commented
I am playing around with importing data from JSON files and am tangling with trying to convert JSON Date/Time to something that I can work with. This could be a lot easier couldn't time? TZ seems to be something that is always such a pain - can you help me not shoot myself in the foot.....repeatedly?
-
Toby Fulton
commented
Please fix this! Just spent quite a few hours trying to figure out why 30/09/2018 is showing as 29/09/2018 due to daylight savings change in New Zealand.
-
Susana
commented
I need to show in a Powe BI report the last refresh date/time of the data.
I keep the date of recharged in a table with a single row and a single column.
But this is the UTC time.
Now I return the one from Spain, but I would like to be able to return the local time of every user.I think that Power BI should have a function to get the time zone of the current user who is using the Power BI service.
-
Firmin
commented
Hi,
Please try DateTimeZone.RemoveZone(DateTimeZone.ToLocal(DateTime.AddZone([#"Date_UTC"],0)))
Hope it helps !
-
Cary Schweitzer commented
This would make my life soooo much easier.
-
Anonymous
commented
Aggreed on this one. All my data is in UTC but when reporting, everything needs to be at local time
-
Kevin H
commented
Yep, I wish I could do at least one of the following...
1. Take my date/time values stored in the data model (e.g. in UTC), and create a DAX measures to return the date/time in the Power BI Service user's local time zone. (And, another DAX measure to say what the user's local time zone is.)
2. Store date/time/timezone values in the data model ... and, have a way for the Power BI Service to convert those values, in reports, to the user's local time zone.
-
Jeff M
commented
Native timezone handling of Daylight Savings Time during timezone conversion would be beneficial to me as well. I am charting purchases by hour, and the hour of the day obviously is not going to convert properly year-round with a simple offset against a date column that is stored in UTC. My target db is MySQL and I realize that MySQL does support timezone conversions IF the timezone table data has been loaded by the DBA. It has not in my case, and the quick workaround would have been for me to take care of this in PowerQuery.
-
larsts
commented
There are workarounds when viewing In Power BI Desktop: https://blog.crossjoin.co.uk/2017/03/28/daylight-saving-time-and-time-zones-in-m/
But this does not work when report is published to Power BI service.We have not found any techniques which work when published to service however - apart from explicitly handling DST and converting to a specific time zone.
For us - this is a major hindrance.
-
Kendall Lister
commented
It's not just a new feature that's needed - the Power BI desktop client just doesn't seem able to handle dates properly when used in some time zones, e.g. Australian Eastern Standard Time. Essentially the same problem Mich I reported for October 2017 happened again in April 2018, as per:
https://community.powerbi.com/t5/Desktop/April-fools/m-p/391979
https://community.powerbi.com/t5/Issues/Issue-with-April-Ist-2018/idi-p/404711
and so on.
-
Jorge
commented
very, very needed!