Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
AndrewDang
Helper IV
Helper IV

Set local time zone Power BI Service

Hello all;

 

I have an issue with setting the right local time for my Power BI Service. I have the same measure to pull the current time. My Power BI desktop file pulls up 1/16/2017 4:36:02 PM which is the corrent local time. However, my Power BI Service file pulls: 1/17/2017 12:36:02 AM, which is 8 hours ahead of the time. I am wondering where do I go about setting this local time on the Power BI Service side?

 

Thanks;

Andrew

 

18 REPLIES 18
Anonymous
Not applicable

For those with access to a SQL Server instance, use DirectQuery from a SQL server data source and paste the following T-SQL:

 

SELECT CONVERT(Time, GETDATE())

 

This will use the database time zone. Set the page in Power BI service to auto refresh visuals every minute (or more), make a card with this sole column, and you will have the current time in your local time zone. 

 

If making measures to account for daylight savings with time oriented data, create a table from SQL server witht the following code:

--Daylight Savings check
SELECT * FROM sys.time_zone_info
Where name = 'Central Standard Time'

Obviously, use the time zone of your preference/choice. You will return a row of data, including a column that tells whether or not you are in daylight time. You can then write measures in two parts, IF(is_daylight_time = False, blah blah..., ELSE insert different parameters for when it is daylight time. You can also create a UTC offset adjuster from this that is dynamic based on the second column, wich is offset. 

drag0ne
Regular Visitor

Power BI service operates in the UTC time zone, and evaluates all locale based time functions in that time zone. Power BI Desktop evaluates them according to the locale of the user.

jackisusingpowe
New Member

After struggle for few weeks, following solutions should work. A simple setting change:

 

On your powerBi desktop, go to File ==> Options and Settings ==> Options

On the dialog opened, select Reginal Settings under current file

Change the Locale for import to the English (your own country)

 

In my case, I choose Singapore and it work.

 

Hope this help, cheers

that only works if you are not using Power BI Service, unfortunately you can't set a local time with Power BI Service is one of the limitations of this product. A workaround is to use a fuction such as NOW()+TIME(8,0,0) but still you won't be able to accurately use the date slicers.

This doesn't work for me.
i have a similar problem

 
Rajiv
Advocate I
Advocate I

I was having this issue where the times that showed in local time zone on my PowerBI Desktop, turned into UTC when published on cloud service. I was able to solve this by first adding a local timezone as table (see Showing Specific Timezones in Power BI Service (Last Refreshed Time) - YouTube). And then mark the resultant table as a Date Table as shown below. Once it is marked as a date table, it almost resets the time zone on your power bi service to local. 

Rajiv_0-1642555671212.png

Rajiv_1-1642556368884.png

 

 

P.S. This also worked on PBI Report Server where I was having the same issue.

Hi @Rajiv,
Your solution seems the most elegant but I am not quite following what you are saying. I am currently using a Calendar table which is marked as date table already. Should I be adding a column to that date table and adding a local timezone calculation to it? 

I added a new table along side my other data sources. It just has one record of the local time. Mark that as Date Table and the other date times follow accordingly.

let
Source = Json.Document(Web.Contents("http://worldtimeapi.org/api/timezone/Australia/Adelaide")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"abbreviation", type text}, {"client_ip", type text}, {"datetime", type datetimezone}, {"day_of_week", Int64.Type}, {"day_of_year", Int64.Type}, {"dst", type logical}, {"dst_from", type datetime}, {"dst_offset", Int64.Type}, {"dst_until", type datetime}, {"raw_offset", Int64.Type}, {"timezone", type text}, {"unixtime", Int64.Type}, {"utc_datetime", type datetime}, {"utc_offset", type text}, {"week_number", Int64.Type}})
in
#"Changed Type"

coolbear_lq
Regular Visitor

Hi all,

 

After reading all of your posts, I actually came out my own solution.

 

Why not getting the current date and time from the database server ( if there is one) ? For my case, I am pulling the data from a SQL Server database into Power BI data model. While the Power BI service has a different time zone, my database server has the right time zone.

 

So I wrote a simple SQL Query and import it into Power Query as a table:

 

SELECT RefreshDateTime = GETDATE(), RefreshDate = CONVERT(DATE, GETDATE())

 

This is a TSQL for SQL Server and it might need to be changed for other database server.

 

Hope this helps.

 

Bill

wont this return a date time relative to when the refresh for the data was ran from the SQL side?

Anonymous
Not applicable

Nope, the folks at power bi (Microsoft) dont support anyway to refresh the service in any other time zones,  Only UTC time.  So, a filter for relative date of today, gives us today in UTC time, not the other 23 time zones.  I have seen many forums and requests to have this fixed with no reply, nor suggestions to get around it.

 

I finally found a way myself but had to add date adjustments and realtive time and adjust like crazy to outsmart the BI service.

 

It seems like a world wide problem that has yet to be addressed and communicated to the community.  A simple setting in the service to refresh in a specific time zone on the service would be the simplest way

I'm going crazy with relative filters. In EST time zone - so I've noticed that as of 8 PM all dashboards with relative filters start working incorrectly. Any good solution for DirectQuery/Relative filter issue? Thank you!

Anonymous
Not applicable

I had to create a create a bunch of duplicate columns for the dates I was trying to report on.  Then adjust for the time diff  Your case this time of year is -4/24.  During winter its -5/24

Reporting Date = Appraisal[CreatedDate] -7/24

Now = NOW() - 7/24

PT Date = DATEDIFF(Appraisal[Now], Appraisal[Reporting Date], DAY)

 

 

 

Then use the last one as a visual filter with advanced filtering and set to show only value of zero.

 

I use the ‘now’ as a unique table as well to then show in the web version the real time its reporting on not the UTC time the programmers think we all work in.  It will be off in your desktop version.

 

Anonymous
Not applicable

How do you then imprt this sql statement into power Bi?  

I have a silimar issue, we want to be able to track our daily sales throughout the day in real time.  Its fine right up to the point in our day where the power Bi servers see 'Today' as midnight in the next day.  We are not on UTC time, so at 4 or 5pm depending on the daylight savings calendar the data goes blank and while we are still in today, power BI is in tomorow

coolbear_lq
Regular Visitor

Hi all,

 

After reading all of your posts, I actually came out my own solution.

 

Why not getting the current date and time from the database server ( if there is one) ? For my case, I am pulling the data from a SQL Server database into Power BI data model. While the Power BI service has a different time zone, my database server has the right time zone.

 

So I wrote a simple SQL Query and import it into Power Query as a table:

 

SELECT RefreshDateTime = GETDATE(), RefreshDate = CONVERT(DATE, GETDATE())

 

This is a TSQL for SQL Server and it might need to be changed for other database server.

 

Hope this helps.

 

Bill

v-sihou-msft
Employee
Employee

@AndrewDang

 

On Power BI Service, the now() function will return the UTC time. We can't set the time zone on Power BI Service. To get the correct local time, we can add time different in your DAX formula, or use Power Query to get the local time zone datetime. For more details, please see: Solving DAX Time Zone Issue in Power BI.

 

Regards,

Thanks @v-sihou-msft

 

The website appears to be down at this time. http://radacad.com/solving-dax-time-zone-issue-in-power-bi. i am wondering if you have an alternative website?

 

I had researched a little further but no one seems to be able to provide a clear resolution for this yet, including this one: http://community.powerbi.com/t5/Desktop/Convert-UTC-to-local-time-zone-in-DAX/td-p/43328

 

Thanks;

Andrew

 

@AndrewDang,

 

Try this for ideas: https://community.powerbi.com/t5/Desktop/UTC-to-AEST/m-p/188878#M83075

 

I agree though, for something so simple as being able to specify a 'local' timezone (such as in your user profile) I'm surprised this is such a difficult task...

 

-Greg.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors