Christiaan B. on 29 Aug 2017 05:15:12
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.
- Comments (33)
RE: Allow formatting datetimezone fields to user local timezone
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.
RE: Allow formatting datetimezone fields to user local timezone
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.
RE: Allow formatting datetimezone fields to user local timezone
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
https://community.powerbi.com/t5/Desktop/Issue-with-filter-after-uploading-April-2018-data/m-p/399454
and so on.
RE: Allow formatting datetimezone fields to user local timezone
very, very needed!
RE: Allow formatting datetimezone fields to user local timezone
Would like to feel more confident with a native solution rather than having to apply custom functions in M
RE: Allow formatting datetimezone fields to user local timezone
Yes, this is very needed! Thank you!
RE: Allow formatting datetimezone fields to user local timezone
Yes Please consider this as a critical priority.
RE: Allow formatting datetimezone fields to user local timezone
Yes, this would be great. I have data coming in as UTC and I want to easily convert it to my local time zone.
RE: Allow formatting datetimezone fields to user local timezone
Agree this is needed. Spent hours trying to decipher why date/time of 1st October 2017 12:00:00 converts itself to 30th September 2017 11:00:00 in reports and hence reports data under the wrong month. (Sydney - AEST - changes to daylight saving at 2am on the 1st October 2017)
RE: Allow formatting datetimezone fields to user local timezone
Certainly would save a lot of time. Previously I had to actually lookup the DST times for the target timezone for the next 5 years, then come up with a hard coded m query to convert. After 5 years, it won't be displaying data properly and i'm sure i'll be somewhere else.