Power BI Service recognize local time zone for TODAY() and NOW() time intelligence functions
As of now, the reports that I create on desktop utilize a lot of time intelligence functions and relative date filters. This is completely fine so long as I view the report on the desktop. Once I publish the report up to the service and then utilize those visuals in dashboards I run into this issue. All of my time intelligence functions (TODAY & NOW) and relative date filters convert to UTC.
This issue causes my data to roll over to the next day 5 hours early which affects all of my subscriptions.
It would be absolutely fantastic if Power BI service could recognize local time zone or even if we could manually set our time zone so that these measure work correctly.
It's not even just measures. It's anything to do with date's. Whether they're used in a measure or just a table that displays the date.
The issue is that reports existing in the service are hosted by servers in utc time. So when the service determines what the "system" time is, it will always be in UTC.
The only workaround I've found is to subtract/add a static number of hours you need to make the datetime display in your local time. I'm in United States Central Time so I use:
-TIME(6,0,0) in CDT and -TIME(5,0,0) in CST.
This is suboptimal for users in a regions that recognize daylight savings time; since you'll have to update your formula twice a year to subtract or add an extra hour.
Please, please make this a priority, ms!!
Nagasatya Devarakonda commented
Any update on this?
Any update on this issue so far?
This is important. When connected to an SSAS live connection, a DAX formula can't be used as a work around.
Time is fundamental.
The ability to specify time zone settings by report is critical for everyone in the world except London.
Yesenia Soriano commented
Yes please can we get this fixed. I have many reports that go blank at 4pm pacific time!
This is also a real issue for our company and can only be resolved by Microsoft. The only solutions/workarounds that are possible will either avoid using relative dates all together or a formula that will only fix the issue for a particular relative time frame.
The main problem is that an individual only know to do this once they have been burned. There is almost no way to know intuitively beforehand. The current implementation is deceptive as the intended behavior changes as you publish it to the service. There are many ways that this can be resolved or made more explicit. Microsoft needs to implement one of them ASAP.
Be sure to raise an issue through your support contract. This should be treated as a bug and not a feature request.
Any update on this?
David Cornwell commented
My recollection (and the way that this idea has been described) is that it used to only be an issue in the Power BI Service. However, I now have the same problem with the relative date filters on PowerBI Desktop which also use UTC. Realy hoping for some development focus on this soon.
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.
David Cornwell commented
Wow....shocked that this is an issue. PowerBI really needs to get this sorted.
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.
Philippe Allard commented
yes ! We need this badly.
Please release this fix
Yes, this is very important for reports that should work globally.
We expected that "cloud-based ... business analytics tools ... suite for (world-wide) organization to consume on the web" - would support the use of local time zones.
(With only UTC it only fits Iceland and some countries in west Africa... All others are puzzled by the indicated date & time.)
Best would be if your new implementation would do:
1. Get the current time zone setting from the connected browser
(see: https://www.w3schools.com/jsref/jsref_gettimezoneoffset.asp )
2. Allow (via dedicated datetime methods) filtering and display to use that request specific user time zone
The very same record would show 01:40 May 1, 2018 in Australia
But: 17:40 April 30, 2108 in Switzerland
And: 11:40 April 30, 2018 in New York
Best regards, David
This is CRITICAL!
This would be super useful!
Sylvia Vargas commented
This would be especially useful to display on headings as well without requiring a table to contain this information.
Please, Please, Please, add this ASAP.
Its crazy that we cant track daily sales past 4pm, when the UTC clock rolls into tomorrow. 23 other time zones exist and cant get accurate data in real time until you fix this.
I have tried using a on premise gateway, and scoured the forums looking for a solution.
You MUST fhis issue ASAP
This is a major limitation to the otherwise great time intelligence functions.