cancel
Showing results for 
Search instead for 
Did you mean: 
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

 

10 REPLIES 10
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?

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

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
Microsoft
Microsoft

@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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.