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

Hi msommerf 

Add a calculated column to your calendar table CalendarT: 

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_
    )
VAR lastOctoberSunday_ =
    CALCULATE (
        MAX ( CalendarT[Date] ),
        MONTH ( CalendarT[Date] ) = 10,
        WEEKDAY ( CalendarT[Date], 2 ) = 7,
        YEAR ( CalendarT[Date] ) = currentYear_
    )
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

Thank you for responding so quickly.

When I use the supplied formula, I get a column of 0's with No 1's as follows:

 

  • 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.