Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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"
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...
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"
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.
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
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.
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 @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"
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?
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!
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.
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
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.