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

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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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.