How to correctly calculate Today using DAX

Colin Maitland, 26 September 2024

I often want to calculate and use date ranges that are relative to today in various Power BI Online reports. However, there is no DAX function that calculates the correct date considering the time zone and daylight-saving time when the time zone is something other than UTC.

In this article I will demonstrate an approach which makes use of either the Dataverse Connector or the SQL Server connector to accurately calculate the correct date for today. Either connector may be used if connecting to a Dynamics 365 Online database and the SQL Server connector may be used if connecting to a non-Dynamics 365 Online SQL Server database.

These DAX functions return the current date and time in the time zone of the server. In Power BI Desktop this is the time zone of the computer and in Power BI this is the UTC time zone.

· NOW() returns the current date and time.

· TODAY() returns the current date. The time is always returned as 12:00:00 AM.

These DAX functions return the current date or date and time in the UTC time zone regardless of whether they are executed in Power BI Desktop or Power BI Online.

· UTCNOW() returns the current date and time in the UTC time zone.

· UTCTODAY() returns the current date in the UTC time zone. The time is always returned as 12:00:00 AM.

The time zone I want to calculate for is New Zealand which is UTC+12:00:00 NZST and UTC+13:00:00 NZDT. Daylight Saving Time for New Zealand for 2024-2025 starts at 2:00 am on 29^th September 2024 and ends at 3 am on 6 April 2025.

The image shows the results returned by these functions in Power BI Desktop on a computer using the New Zealand Time Zone at 29/09/2024 8:00:00 am (NZDT).

This image shows an  example of the results returned by these functions in Power BI Online when the date and time is 29/09/2024 8:00:00 am (NZDT). Power BI Online runs in the UTC Time Zone.

I want to use DAX to calculate the correct New Zealand date for today regardless of whether the calculation is being executed in Power BI Desktop or Power BI Online. However, there is no DAX function that accomplishes this directly and works in both environments. I could use one of these two measures:

This measure calculates the correct New Zealand date for today when not in daylight-saving time.

This measure calculates the correct New Zealand date for today when in daylight-saving time.

The problem is how to know when New Zealand Daylight Saving Time is in effect or not.

Reza Rad suggests a method in his article Solving DAX Time Zone Issue in Power BI - RADACAD. His method requires the use of a Web Query with Power Query using a connection to an external web hosted time server. His method accurately returns the New Zealand date and time for today based on the date and time of the last refresh of the query.

My approach returns an accurate date for today using the Date/Time returned by the DAX UTCNOW() function and then adjusting that by the Time Zone Offset returned by a Power Query. The Time Zone Offset returned by the query will always be either +12 for NZST or +13 for NZDT with a maximum inaccuracy of +/- 1 hour depending on when the query was last refreshed relative to the start and end of daylight saving. My approach does not require the use of an external web hosted time server or api. I instead use a connection to the Dataverse or SQL Server database, from which I am loading data anyway.

Here is my Power Query using the Dataverse Connector.

Here is my Power Query using the SQL Server Connector.

The key aspect of my SQL query is the CONVERT(datetime, GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time') AS 'Last Refreshed (NZST)' clause to return the date/time/zone for the current date and time converted from the UTC to the New Zealand Time Zone. This approach automatically takes daylight saving into account. Note: Even though this calculation refers to ‘New Zealand Standard Time’, if New Zealand Daylight Saving Time is in effect a NZDT rather than NZST date and time is be returned.

I then add a custom column named UTC Offset (New Zealand) that calculates the time zone offset based on the difference between the UTC and NZ times returned by the query. This will return a value of either 12 or 13 depending on whether New Zealand Daylight Saving is in effect or not.

I can now add a measure to my semantic model return this offset as shown here. This simply returns the value of UTC Offset (New Zealand) from the ‘Last Refreshed’ table in which there is only ever one row. The name of this measure is Last Refreshed UTC Offset (New Zealand).

I can now write my Date Today measure using DAX as shown here.

This measure correctly returns 29/09/2024 rather than 28/09/2024 when the current New Zealand time of day is before 1 pm and returning 29/09/2024 when the current New Zealand time of day is after 1 pm on 29/09/2024.

Finally, I could just use the date portion of the New Zealand date and time returned by the query. However, that approach increases the level of inaccuracy based on when the query was last refreshed. What if the refresh hasn’t occurred as expected and is out of date? The approach I have described in this article reduces the potential inaccuracy by using the DAX UTCNOW() function and only the time zone offset from the query.