Generating DateTimes in Power BI can often be a sneaky trap. Things may appear one way when we create and publish the report, but then fall out of sync once the report refreshes. Let’s explore some of this behavior with a sample Power BI report and discover a few ways to prevent it from happening.
First let’s acknowledge that there are a LOT of ways to generate DateTime in Power BI. This list is by no means exhaustive – but it captures a number of the ways we could go about doing this. For reference purposes the cards below are from a Power BI desktop file that was refreshed at 6:26:30 AM local time.
Before we step through this list – let’s quickly publish these cards to the Power BI service and refresh. The refresh part is important – as the cards will continue to post to same time shown above until the model is asked to refresh.
There it is! Nearly every datetime strategy we employed Power BI fails to maintain the expected time when it is refreshed in the Service. Why?
Well – for starters when we use DateTime functions on our local computer – Power BI references Windows Date/Time to populate these date time values. Once the measures or formulas are shipped off to Power BI’s servers – this connection is stripped away in favor of the more universally consistent UTC format.
Let’s look at each one of these examples in more detail to understand why it does or doesn’t work for this task.
Using SELECT GETDATE() from Azure SQL DB
This was easy to spot (because it returned the wrong time from the very start). For the non-SQL audience – this strategy involves calling the Date Time of the selected Database by entering Select GetDate in the Advanced options section of the Get Data menu.
While this strategy doesn’t work for Azure SQL (which Microsoft makes clear doesn’t support Time Zones ), it WOULD work for Azure SQL Managed Instance (MI) or SQL Server On Prem.
In all of these cases – once the time has been imported from the database – it will not be altered by Power BI and will remain constant to all report users regardless of their viewing location.
Using DAX to generate Date Time
The NOW() formula always initially seems to satisfy our needs – as it will return the date time of the computer being used to author the report. However, hidden in plain site on the formula page is a warning:
The formula changes to UTC once it is refreshed in the Service. Yes – we could ‘offset’ the time from UTC to our local time, but we would also have to juggle the twice-a-year impact of Daylight Saving on that conversion, which can be a bit tricky to pull off using just DAX.
Using Power Query – DateTime.LocalNow()
Power Query has a bunch of DateTime functionality – but perhaps the most compelling is using the DateTime.LocalNow() formula to return the local time – and then immediately convert it into DateTimeZone format.
The assumption here is that since we changed the format to DateTimeZone (which is better than DateTime) – it should hold constant once it refreshes in the service. Unfortunately – this is not the case. As the example above demonstrates – this format will also convert to UTC once refreshed in the Service.
This also happens if we try using the more eloquently named: DateTimeZone.FixedLocalNow().
Unfortunately – the “fixed” part of this formula does not mean it will maintain the local time zone once refreshed in the service, – it instead means that the returned value “will not change with successive calls…” This formula is intended as a partner to DateTimeZone.LocalNow, which may return different values (think milliseconds) over the course of execution of an expression in Power Query.
The solution to this Power Query conundrum is to use the DateTimeZone.SwitchZone() to explicitly set the time zone of a returned DateTimeZone value. Additionally – let’s use DateTimeZone.LocalNow() and not DateTime.LocalNow().
Of course this solution comes with the same Daylight Saving hurdle, which requires the time zone adjustments to switch at the start and end of DST – which in the US occurs on the 2nd Sunday of March and 1st Sunday of November.
Using an API to return the correct Date Time
There are likely a ton of API endpoints that will return some variation of time and date. Each comes with its own requirements or perhaps a cost. Additionally – if the API doesn’t have an SLA (service level agreement) that specifies it’s uptime reliability – it’s probably best to keep them out of production level reporting.
The two APIs I’ve chosen for this testing are:
The process for calling an API in Power BI is very simple for these two APIs – since they don’t require an Authorization Key.
- Select Get Data From Web as the Source:
- Enter the URL specific to the Site and DateTime desired (in this case it is Eastern Time).
That’s it! Power BI takes care of the rest – like handling the JSON that is returned and converting it to a Table.
The next part is important because it is the reason why one of these two attempts maintains the correct time – and the other is converted to UTC.
The returned TEXT value from the API must be changed to and kept in DateTimeZone Format. If, for example, the time is changed from DateTimeZone to DateTime, Power BI will swap it out with UTC upon refresh. It must remain in DateTimeZone for the time to return correctly upon refresh.
- If using Azure SQL MI or On-Prem SQL DB – simply query the database time using a SELECT GETDATE() statement.
- With Power Query – use DateTimeZone.SwitchZone to specify the desired time zone and see this video for adding DST corrections.
- Call an API and keep the format in DateTimeZone. (Also make sure the API is reliable before including it in any production level processes).