How can we improve Power BI?

Time Zone Conversion Accounting for Daylight Savings Time (DST)

We need a way to display data in our local timezone accounting for daylight savings time. The data source stores the dates in UTC but we need reports to show local time. A native and easy way to do this in PowerBI would be a huge help. There are many blog posts and help articles floating around with solutions people have attempted but it's a simple issue that so many users would benefit from having solved centrally by PowerBI itself. Please consider!

161 votes
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

Katy Ionis shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

17 comments

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

    Desperately need this. Reporting is unreliable without easy way to show in local time

  • Michael commented  ·   ·  Flag as inappropriate

    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.

  • Eric Thomas commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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.

  • Firmin commented  ·   ·  Flag as inappropriate

    Hi,

    Please try DateTimeZone.RemoveZone(DateTimeZone.ToLocal(DateTime.AddZone([#"Date_UTC"],0)))

    Hope it helps !

  • Anonymous commented  ·   ·  Flag as inappropriate

    Aggreed on this one. All my data is in UTC but when reporting, everything needs to be at local time

  • Jeff M commented  ·   ·  Flag as inappropriate

    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.

  • Kendall Lister commented  ·   ·  Flag as inappropriate

    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.

  • Ben Hainsworth commented  ·   ·  Flag as inappropriate

    Would like to feel more confident with a native solution rather than having to apply custom functions in M

  • Michael commented  ·   ·  Flag as inappropriate

    Yes, this would be great. I have data coming in as UTC and I want to easily convert it to my local time zone.

  • Mich I commented  ·   ·  Flag as inappropriate

    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)

  • Ryan commented  ·   ·  Flag as inappropriate

    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.

Feedback and Knowledge Base

Ready to get started?

Try new features of Power BI today by signing up and learn more about our powerful suite of apps.