Power BI – Did you know ALL Power BI Services Servers are in UTC? Now how to handle it for Dates & Times
I was working for a customer recently, where the requirement was to create a slicer which would only show the data for Today, Yesterday and then all other days.
When I was consulting with the other developers they were unaware that the Power BI Services Servers all have the Time zone set to UTC.
And this applies to ALL Power BI Services Servers, no matter which data center they are located worldwide. This was an unknown fact. And I have had this confirmed by the Power BI team.
In the steps below I will demonstrate the Times for the Power BI Server Services.
And then I will demonstrate how I achieved the Today, Yesterday and other days slicer, so that it would display correctly in the Power BI Service for the local users.
Power BI Service Times
I am currently living in the Gold Coast in Australia, which is UTC + 10. And below I will demonstrate when I uploaded my Power BI model to the Power BI Service, how the times were different.
As you can see above the time in UTC (Power BI Service) was 7:51 PM on 02 Oct 2017, whilst where I am it was 5:51 AM on 03 Oct 2017
NOTE: If I wanted to find out what my UTC Time Zone was I would go to the following URL to find out: https://www.timeanddate.com/worldclock/
- I could then type in where I was living (Brisbane), which then resulted in the Time Zone being shown.
What I did to get the above times is I created one measure which is based on the Local Time of the Server (Date Time of Power BI Service) and I created a table in the Query Editor in which I took the Local Time of the Server and added 10 hours (Date Time where I am (UTC + 10)
Creating the Today, Yesterday and Other Days Column – Which I then used as a Slicer
In the steps below I will demonstrate how I created a new column in the Query Editor which would show Today
(for my current date), Yesterday (for the date going one day back) and Other Days for all other days.
- I went into the Query Editor and to my Date table.
-
The first thing that I did was to create a column called Current Date, which is my current date
- As you can see above I added a Duration of 10 hours to the FixedLocalNow().
- I then also used the Date.From so that it only returned the Date
- NOTE: As I explained previously this is because when I upload it to the Power BI Service I will need the Date Time to be at my local time zone which is UTC + 10
-
I then added a Conditional column to see if my column called “Date” equals my “Current Date” and if it is then it is the “Current Date” otherwise “Other Date”
-
I created another column for Yesterdays Date
- As you can see above, the syntax is very similar, with the addition of going back one day.
- I added a Duration of 10 hours to the FixedLocalNow().
- I then also used the Date.From so that it only returned the Date
- As well as the Date.AddDays so that I could go back one day.
- NOTE: As I explained previously this is because when I upload it to the Power BI Service I will need the Date Time to be at my local time zone which is UTC + 10
-
This then allowed me to create my final conditional column in which I then defined if it was Today, Yesterday or Other Day
- I then loaded this into the Power BI Data Model.
Testing the Date Time in the Power BI Service with UTC Time Zones
I then uploaded my Power BI Desktop Model to the Power BI Service, to ensure that it would work for my local Date Time zone.
- Next to test it I created a Slicer off the column called “Today/Yesterday – Other Days”, and then put in all the Dates from the Calendar table to see if it worked as expected.
-
When nothing was selected on the Slicer it showed all the Dates.
-
Next when I clicked on Today in the Slicer it showed me Todays Date – 03 October 2017
-
Next when I clicked on Yesterday in the Slicer it showed me Todays Date – 02 October 2017
Conclusion
I have demonstrated in this blog post how the Power BI Servers are in UTC.
You can test it out below.
As well as shown how to ensure that when my data refreshes, and is used within the Power BI Service it will be relevant to my Time Zone.
If you have any comments or questions please leave them in the section below.
How did you create those buttons ? Today, Yesterday and other day
Hi there,
Once I had created the data in the Query Editor, I then used the Chiclet Slicer in Power BI to display the values from that column
[…] part of completing this solution I also included the UTC Offset, which I did blog about previously Power BI – Did you know ALL Power BI Services Servers are in UTC? Now how to handle it for Dates &… which will ensure when the Power BI Desktop file is uploaded and refreshed within the Power BI […]
Hi Gilbert,
thanks for this blog and sharing of this information. I am a new user of PowerBI and discovered this tutorial through a google search, however I am finding that the UTC time difference is not an issue for me and I am also in Australia (Sydney).
When I don’t use the formula to increase the time, my report displays the correct local time. When I did include the formula (I used +11 due to daylight saving), I’m showing a day ahead of local time.
Perhaps there has been some development that has now made this redundant?
Cheers,
Paul
Hi there
What happens when you upload it to the Power BI Service and then refresh it via the Gateway?
Also what did you put in to get the local time?
HI, I am in Auckland and my published reports are only refreshing to the next day data after UTC midnight. My calendar table current dat is set to UTC +12. My on premise refresh time zone is set to UTC + 12. What else needs to be changed to get my data to click over to today’s data at midnight UTC +12?
Hi there
What you would need to do as in the blog post is to offset your Date/Time by 12 hours.
When you do set up the scheduled refresh make sure to set the timezone to your local timezone (GMT +12) so that you can specify the time you want it to update?
Hi Gilbert, I a doing the same thing here
= Table.AddColumn(#”Added Custom: TimeDiff”, “Sent Time”, each (Time.From([SentDate2]) + #duration(0, Number.From([TimeDiff]),0, 0)))
It seems to look fine in the Power BI service initially, but not even on a refresh, just checking on the same report a few hours later, the time value seems to revert back to what it was before, even with the addition of time difference. Any ideas?
Hi Ian, thanks for the comment.
How are you getting the [TimeDiff]?
What happens if you hardcode the TimeDiff does it then work after a refresh?
@Gilbert… I am having almost the same challenge… I have a 1-hour difference in the source file of the report. My data source is my Outlook calendar but when I visualize in power BI service, the time changes and reduces the time by minus 1 houer
Hi Adeleke,
You will need to modify the time in Power Query to add in the extra hour difference?