When you need to report hourly, Daylight-saving time switches can be a real issue. Here’s how I managed to solve it.
Usually, we create reports with the granularity of Days or lower.
Some time ago, I worked for a client which creates reports on the energy consumption of their production lines, which run 24/7.
Their data is aggregated to 15 minutes.
In such a situation, the switch to daylight saving time and back to solar time can cause issues in the report.
For this article, I created a table based on a Date- and a Time-Table with a time series of one row per minute and a value of 1 for each row.
This way, I can straightforwardly show the switches’ effects.
When I load this table into Power BI, I get the following report when I look at data at the monthly level:
When I look at the daily level on one of the months, without a switch, I get the following report after adding a Tooltip on the data at the level of hours:
Now let’s look at the daily Data for a month with a Switch:
As you can see, we have a spike in the data in Fall. In Spring, we will get a gap in the data when we switch from solar time to daylight saving time.
The issue is that, in reality, the data has no gaps and spikes, but we see them because of the switches.
This can confuse the consumer of the report.
The first step for a possible solution is to recognise the days with a switch.
For this, I used the following T-SQL code when I loaded my Date-Table in my Azure SQL database:
SET @TimeZone_Offset =
DATEADD(hh, 4, @DT),
SWITCHOFFSET(DATEADD(hh, 4, @DT),
DATEADD(hh, 4, @DT
AT TIME ZONE ‘Central European Standard Time’)
The Variable @DT contains a Date.
Then, I add four hours to the Date to the first parameter of DATEDIFF().
The next step is to use SWITCHOFFSET() as the second parameter of DATEDIFF() to switch to the UTC zone and get the difference between the local time zone and UTC. You need to set your time zone when calling the DATEPART(TZOFFSET, …) function.
After writing the data to the Date-table, I use the following code to set a special value on each row with a time switch:
,CASE [TimeZone_Offset] — LAG([TimeZone_Offset], 1, 1)
OVER(ORDER BY [DateKey])
WHEN 1 THEN 2.5
WHEN -1 THEN 1.5
END AS [TimeZone_Offset_Chg]
SET [TimeZone_Offset] = [R].[TimeZone_Offset_Chg]
FROM [dbo].[Date] AS [D]
INNER JOIN [Result] AS [R]
ON [R].[DateKey] = [D].[DateKey];
I use the LAG() function to compare the actual with the previous row to see if the value has changed.
Then I set particular values (1.5 in Fall and 2.5 in Spring) when something has changed.
This way, I can find the Days with a Switch between the solar and the daylight-saving time.
What follows is how I want to handle this information.
For example, I want to show in my report if a switch happened on that day or not.
To achieve this goal, I created a new Measure to compile a dynamic commentary:
,”Switch of Daylight saving time happened”
When a switch happens on a specific day, HASONEVALUE() returns false for the column [TimeZone_Offset].
To make this possible, I set up my Demo dataset to switch the TimeZone_Offset at the correct time:
Consequently, I get more than one value when I check the column [TimeZone_Offset] for a Day with a Switch. So, HASONEVALUE() return FALSE.
In Power BI, the result looks like this:
On a typical day, I show the Text “Normal OP”:
This small addition lets the consumer know what happened and understand the data correctly.
Although this is a rather exotic issue, I learned a lot about working with time zones when working on this issue.
You might find another way how you want to show your results in such a case.
My client, for example, is thinking about smoothing out the values over the day when the switch happens in Fall. I don’t know how he will handle the gap in the data in Spring yet.
Anyway, my approach lets you open up about how you want to cope with the situation. It only provides you with the toolset to do it.
It’s up to you and your users to work out the details to cope with the time switches.