In Power BI, if you have been using date and time data for long enough, you may have noticed a peculiarity with how your time works in Power BI Desktop, compared to Power BI Service. This is because Power BI Desktop uses your computer’s local time, whereas Power BI Service uses UTC time. This can cause a plethora of problems with reporting the correct date, functions choosing current day, etc. Below are 3 possible methods to get around this issue.
Method 1: Connect to a web source
One method to get the correct DateTime is by connecting to a web service to return the local time of whatever region you need. After connecting to this source and doing some simple reformatting, you have an updatable and reliable way to get your preferred time zone. In our case, we most recently used this as a simple way to show the most recent refresh date of the report in Pacific time.
You can see a full list of this site’s time zone options here: https://worldtimeapi.org/timezones
Method 2: DAX Measures
Probably the easiest method to use is by simply adding or subtracting the difference of your time zone and UTC time from the DAX NOW() function.
This method is very easy to put together, simply Google your time zone offset or look it up on a site like below. Then, add or subtract the total from the now function, and you have your proper time in Power BI Service.
https://www.utctime.net/time-zone-abbreviations
It is worth noting however, that this method a notable downside. In Power BI Desktop, your time will appear as the computer’s time +/- your offset, rather than UTC +/- the offset, this may make it difficult to test some reporting elements, as they will appear differently in Desktop and Service.
Method 3: Power Query
Power Query has inbuilt functions to deal with this issue, namely the DateTimeZone functions. Using the SwitchZone function, it is easy to select your preferred time zone via the method below. As before you can see the -8 offset for PST from UTC (note the 0 in the optional minutes offset, which can be left blank in many cases). If you have data that needs visibility to your DateTime information within Power Query, this method may work best.
These are three simple methods to get around the date and time subtleties of Power BI Desktop and Service that have repeatedly come in handy at BCS. If you’d like to learn how Bond Consulting Services can help implement Power BI at your company, contact us at sales@lime-falcon-860444.hostingersite.com.
