Forum Discussion

AndrewDang's avatar
AndrewDang
Helper IV
9 years ago

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 whic...
Need Help
coolbear_lq's avatar
coolbear_lq
Regular Visitor
8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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

  • nirvana_moksh's avatar
    nirvana_moksh
    Impactful Individual
    8 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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

      • wildmight2017's avatar
        wildmight2017
        Advocate II
        8 years ago

        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!