Dealing with Day Light Savings time changes in the data

[kremer]

Hi all, 

I am relatively new to Power BI and am experiencing an issue with daylight savings time changes. 

I am working with a timeseries dataset, with a datetime index in local time (UTC+1/UTC+2). Here in the Netherlands, we are switching from UTC+2 to UTC+1 every October (e.g. 28 October 2018 at 03:00, clock went back to 02:00). When importing my data to PowerBI, the timezone information is lost and the date is stored in format "yyyy-MM-dd mm:HH". The problem I am experiencing is in the example above, when the 28th of October contains two entries for the time 02:00, instead of one entry. When plotting this data in a graph, these entries are automatically grouped, which creates a weird effect in my graph (the values are summed up at 02:00). 

I know I can fix this by switching to a UTC format, but this is not my prefered solution, because the graph will be far easier understood for my users if it's in local time. 

Any ideas on how to fix this? 

[Anonymous]

Hi,

Did you solve this?

[CheenuSing]

Hi @kremer ,

Just want to know is it done only for the day October 28 or October 28th to some date later.

How are you loading the data. After loading the data you can go to Edit Queries adn create a custom column of Date & Time with the condition if Date is October 28 the reduce the value by -1 hour.

If you share some data ( not picture ) then it will be easier for me to formulate a solution.

Cheers

CheenuSing 

[kremer]

Hi @CheenuSing, 

Thanks for your response, 

Day Light Savings time runs from the 4th Sunday in March until the 4th Sunday in October. 

My dataset runs across multiple years and is updated daily (new data is added). The user should be able to filter by date, to get a view on what happened on a specific day. On 99% of the dates, the graph is shown correctly, but on those specific days when DST changes occur, the data is not shown in the way I want it. 

In the table you can see that the time is turned back 1 hour at 3 AM, causing double entries in the table (2:00, 2:15, 2:30 and 2:45 are double). 

I don't fully understand your suggestion about changing the data format, do you mean un-doing the time change (e.g. converting all datetimes to UTC+1)? This is a possibility, but does not really solve my problem.