Set local time zone Power BI Service

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. 

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

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

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.

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

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

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,

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. 

Ok, here's how you do this. 

In power query add a new table by clicking "Enter Data" 

Create a column called "Dumb" with a value called "Dumber" (I use this naming convention a lot with powerbi stuff) 

Then Add a column with the formula DateTime.LocalNow()

Then save and close and now you have a Dumb table with an even Dumber way to get Local Date Time values in Powerbi service. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSinNTUotUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dumb = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dumb", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "LocalNow", each DateTime.LocalNow())
in
    #"Added Custom"

The easiest solution I have found was the keep the Date field as "Local Time" and only make the date formatting in the semantic model.

For example, extracting the datefield from web api in dynamics 365 using Dataflow at the end of the data transformation the date field must be "Local Time".

Then when importing the dataflow into semantic model - when you do your "Transform data" section ensure to transform the datefield to "Local Time" (so you should the value like: dd/mm/yyy hh:mm:ss am +hh:00).

Then in the "table view" you can change the date field to dd/mm/yyyy (and it should display the date in your timezone).

Then you don't have to worry about daylight savings...

I had a somewhat similar issue...

I had a column of datetimes in the PowerBI service which are in UTC.  I needed to be able to group the records by date... but based on the date Pacific Time (UTC-7 or UTC-8 depending on daylight saving time).

Was able to use the following code to create the new column... 

let
// This converts the time to PacificTime
 //CurrentYear = Date.Year(DateTime.LocalNow()),
 //DaylightTimeStart = Date.StartOfWeek (#date(CurrentYear,3,14),Day.Sunday),
 //StandardTimeStart = Date.StartOfWeek (#date(CurrentYear,11,7),Day.Sunday),


  Source = Sql.Database("hercules3", "BC"),
  #"Navigation 1" = Source{[Schema = "dbo", Item = "Technical Prospects LLC$Shipping Checklist"]}[Data],
  #"Choose columns" = Table.SelectColumns(#"Navigation 1", {"Created At", "Cleaned By", "Packaged By", "Pre-Pack", "Entry No_"}),
  #"Filtered rows" = Table.SelectRows(#"Choose columns", each [Created At] >= EarliestTimeDateforData),
  #"Changed column type" = Table.TransformColumnTypes(#"Filtered rows", {{"Created At", type datetimezone}}),


  // The "Created at" field is datetimezone in UTC. We need to add a column to return the date (in Pacific Time) so that we can group all records based on this date
  #"+Created at Date (Pacific Time)" = Table.TransformColumnTypes(Table.AddColumn(#"Changed column type", "Created at Date (Pacific Time)", each let
            // Get the year of the UTC datetime
            year = Date.Year(DateTimeZone.RemoveZone([Created At])),

            // Calculate the start and end of DST for the given year (second Sunday in March and first Sunday in November - this works for the U.S.)
            dstStart = DateTimeZone.SwitchZone(
                          DateTimeZone.From(
                              Date.AddDays(#date(year, 3, 8), 
                              7 - Date.DayOfWeek(#date(year, 3, 8), Day.Sunday))
                          ), -8, 0
                      ),
            dstEnd = DateTimeZone.SwitchZone(
                        DateTimeZone.From(
                            Date.AddDays(#date(year, 11, 1), 
                            7 - Date.DayOfWeek(#date(year, 11, 1), Day.Sunday))
                        ), -7, 0
                    ),

            // Determine if the datetime falls within DST or not
            pacificDateTime = if DateTimeZone.From([Created At]) >= dstStart and DateTimeZone.From([Created At]) < dstEnd 
                              then DateTimeZone.SwitchZone(DateTimeZone.From([Created At]), -7, 0) // PDT is UTC-7
                              else DateTimeZone.SwitchZone(DateTimeZone.From([Created At]), -8, 0) // PST is UTC-8
        in
            pacificDateTime), {{"Created at Date (Pacific Time)", type date}}),
  #"Removed columns" = Table.RemoveColumns(#"+Created at Date (Pacific Time)", {"Created At"})
in
  #"Removed columns"