Daylight savings time for whole data set

[Anonymous]

Hi,

I would like to do the following : apply Daylight savings time on my date colmun, which has this format : 04/06/2020 19:00:00

I read various posts on this subject, including this one, which is the closest of what I'm looking for : https://community.powerbi.com/t5/Desktop/Daylight-Savings-Time/td-p/1412956

The difference here is that I want to apply it on a data set with many years (2019, 2020, 2021), and not just on today or current year.

I've tried to come up with a formula to change time depending on being :

-before the "last march sunday"

-between "the last march sunday" and "the last october sunday"

-after "the last october sunday"

Here is the formula I'm trying to use :

Date_UTC+1/2 =
VAR lastMarchSunday_ =
CALCULATE (
MAX ( 'File1'[Date] ),
MONTH ( 'File1'[Date] ) = 3,
WEEKDAY ( 'File1'[Date], 2 ) = 7,
YEAR ( 'File1'[Date] )
)
VAR lastOctoberSunday_ =
CALCULATE (
MAX ( 'File1'[Date] ),
MONTH ( 'File1'[Date] ) = 10,
WEEKDAY ( 'File1'[Date], 2 ) = 7,
YEAR ( 'File1'[Date] )
)
RETURN
CALCULATE(
IF (
'File1'[Date] >= lastMarchSunday_
&& 'File1'[Date] < lastOctoberSunday_,
'File1'[Date]+TIME(2,0,0),
'File1'[Date]+TIME(1,0,0)
),
YEAR ( 'File1'[Date] ))

So, the idea is to have UTC+2 when in "summer time", between the two variables, and UTC+1 the rest of the year, for each year of the data set.

However, I get errors for the last part, indicating that a single value can't be determined for Date.

Any idea of where I'm doing something wrong ?

Best regards,

Martin.

[lbendlin]

You can't use 'File1' -  you need to use 'Calendar'

[Anonymous · Accepted solution]

Hi,

In the end, after various tests, I ended up mixing two solutions :

-First, I created a new column, to get a numerical value for each day :

DateText = INT(VALUE(File1[Date]))

and a Year column, to avoid comparison errors :

Year = FORMAT('File1'[Date],"YYYY")

-Then, I used this formula (the data set starts in the last month of 2019) :

Date_UTC+1/2 = IF(VALUE('File1'[Year])=2019,

'File1'[Date]+TIME(1,0,0), 

IF(VALUE('File1'[Year])=2020,

IF(VALUE('File1'[DateText])>= 43919 && VALUE('File1'[DateText]) < 44129,

'Feuil1'[Date]+TIME(2,0,0),

'Feuil1'[Date]+TIME(1,0,0)),

IF(VALUE('File1'[Year])=2021,

IF(VALUE('File1'[DateText])>= 44283,

'File1'[Date]+TIME(2,0,0),

'File1'[Date]+TIME(1,0,0)))    ))

Thanks for the help,

Best regards,

Martin.

[Anonymous]

Hi,

I can't, I must use the timedate of my data set on the graphs. I just need to adjust it to the right UTC.

Maybe it's best to go back to the formula, isn't there a way to make the formula work ?

It keeps saying the "single value cannot be determined" error, but only for the [Date] lines in the "return" part.

Date_UTC+1/2 =
VAR lastMarchSunday_ =
CALCULATE (
MAX ( 'File1'[Date] ),
MONTH ( 'File1'[Date] ) = 3,
WEEKDAY ( 'File1'[Date], 2 ) = 7,
YEAR ( 'File1'[Date] )
)
VAR lastOctoberSunday_ =
CALCULATE (
MAX ( 'File1'[Date] ),
MONTH ( 'File1'[Date] ) = 10,
WEEKDAY ( 'File1'[Date], 2 ) = 7,
YEAR ( 'File1'[Date] )
)
RETURN
CALCULATE(
IF (
'File1'[Date] >= lastMarchSunday_
&& 'File1'[Date] < lastOctoberSunday_,
'File1'[Date]+TIME(2,0,0),
'File1'[Date]+TIME(1,0,0)
),
YEAR ( 'File1'[Date] ))

Best regards,

Martin.

[lbendlin]

Well, first of all, there are no savings involved. It is called Daylight Saving Time, not Daylight Savings Time.

I would recommend you handle this via your calendar table. For each year you can indicate start and end of DST, and even include the note about the timing of the change if your fact data requires hour level granularity.

It's always funny to read about the trains that have to wait in their stations for an hour during the switchover 🙂

[Anonymous]

Hi,

I'm still trying with the table.

I succeeded in adding the 1 and 2 values at first, because I used a Excel sheet for the calendar.

But, I can't use its fields to fully replace the ones of the data set, (it's only date, and not datetime), so I started created a new calendar table in the Power BI as you suggested.

However, I can't manage to indicate the start and end of DST as you indicate, as well as the timing of the change.

Are there specific formulas for the columns doing that ?

Best regards,

Martin.

[Anonymous]

Hi,

Thanks for the help !

I added a column to my calendar table, with the value 1 for UTC+1 and the value 2 for UTC+2.

I then made the following formula :

Date_UTC+1/2 =

IF ('Calendar'[UTC]=1,
'File1'[Date]+TIME(1,0,0) ,
'File1'[Date]+TIME(2,0,0)

)

But it doesn't work either, with the error "a single value cannot be determined", even if I'm making a calculated column and not a measure.

What am I doing wrong ?

Best regards,

Martin.