Forum Discussion

msommerf's avatar
msommerf
Icon for Helper III rankHelper III
5 years ago
Solved

Daylight Savings Time

I live in the UK and the UTC offset for my date/time values changes twice per year: On the Last Sunday of March & The Last Sunday of October every year.   Does anybody know how I can add a column ...
Need Help
Show and Tell
Tutorial Requests
  • AlB's avatar
    AlB
    5 years ago

    msommerf 

    My apologies, we need to account for the context transition:

    Offset =
VAR currentYear_ =
    YEAR ( CalendarT[Date] )
VAR lastMarchSunday_ =
    CALCULATE (
        MAX ( CalendarT[Date] ),
        MONTH ( CalendarT[Date] ) = 3,
        WEEKDAY ( CalendarT[Date], 2 ) = 7,
        YEAR ( CalendarT[Date] ) = currentYear_,
        ALL( CalendarT)
    )
VAR lastOctoberSunday_ =
    CALCULATE (
        MAX ( CalendarT[Date] ),
        MONTH ( CalendarT[Date] ) = 10,
        WEEKDAY ( CalendarT[Date], 2 ) = 7,
        YEAR ( CalendarT[Date] ) = currentYear_,
        ALL( CalendarT)
    )
RETURN
    IF (
        CalendarT[Date] >= lastMarchSunday_
            && CalendarT[Date] < lastOctoberSunday_,
        1,
        0
    )

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

msommerf 

My apologies, we need to account for the context transition:

Offset =
VAR currentYear_ =
    YEAR ( CalendarT[Date] )
VAR lastMarchSunday_ =
    CALCULATE (
        MAX ( CalendarT[Date] ),
        MONTH ( CalendarT[Date] ) = 3,
        WEEKDAY ( CalendarT[Date], 2 ) = 7,
        YEAR ( CalendarT[Date] ) = currentYear_,
        ALL( CalendarT)
    )
VAR lastOctoberSunday_ =
    CALCULATE (
        MAX ( CalendarT[Date] ),
        MONTH ( CalendarT[Date] ) = 10,
        WEEKDAY ( CalendarT[Date], 2 ) = 7,
        YEAR ( CalendarT[Date] ) = currentYear_,
        ALL( CalendarT)
    )
RETURN
    IF (
        CalendarT[Date] >= lastMarchSunday_
            && CalendarT[Date] < lastOctoberSunday_,
        1,
        0
    )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

msommerf's avatar
msommerf
Icon for Helper III rankHelper III
5 years ago

This is perfect.

Thank you so much for responding.

I just need to get my head around how this is doing the calculation, so that I may learn.

Kind regards

Mark.