Measure to Convert UTC to local time, accounting for daylight savings from Mar 2nd sun-Nov 1st sun

[Nivi_N_26]

Hello. I am creating a dashboard using a direct query from SQL, with automatic page refresh when a change is detected. Because I am using direct query, I can do very little transformation in Power Query. And because I am using automatic page refreshes, I cannot use calculated tables or columns because then the visuals do not refresh when a change is detected.

I have one measure to convert the time from UTC to local (local date = Selectedvalue(table[date/time]) +TIME(6,0,0). 

However, I need a way to condition this statement so if the date falls within daylight savings time ( Mar 2nd Sunday-Nov 1st Sunday), the measure will only add 5 hours . Is this possible? I found several articles on how to do this in power query, but I cannot use power query for this.

I have tried a similar solutions from Solved: Measure to Convert UTC to local time, accounting f... - Microsoft Fabric Community
But it wont fit my requirement as the Day light savings should end on Nov 1st sunday.
My
First condtion will be If date is  from Mar 2nd Sunday to Nov 1st sunday then + 5 hours 
Second condition will be if date is after Nov 1st sunday to before Mar 2nd sunday then +6 hours.

I have tried the code which is only working for my first condtion

Measure = 
VAR _table = 
ADDCOLUMNS(
    ALL(DateTable[Date]),
    "Day",
    WEEKDAY(DateTable[Date], 2),
    "Month",
    MONTH(DateTable[Date])
)
VAR _table1 = 
FILTER(
    _table,
    [Month]=3&&[Day] = 7
)
VAR _shiftdate = 
EXCEPT(
    TOPN(
      2,
      _table1,
      DateTable[Date],
      ASC
    ),
TOPN(
      1,
      _table1,
      DateTable[Date],
      ASC
    )
)
VAR _shiftdate2 = MINX(_shiftdate, [Date])
RETURN
IF(
    MAX([Date])<_shiftdate2,
    MAX([Date]) + TIME(6,0,0),
    MAX([Date]) + TIME(5,0,0)
)

If anyone can help me rewrite the code then it would be a great help


Thanks in Advance.

[v-yiruan-msft]

Hi @Nivi_N_26 ,

Please update the formula of your measure as below and check if it can return your expected result.

MEASURE =
VAR _table =
    ADDCOLUMNS (
        ALL ( DateTable[Date] ),
        "DayOfWeek", WEEKDAY ( DateTable[Date], 2 ),
        "Month", MONTH ( DateTable[Date] ),
        "Year", YEAR ( DateTable[Date] )
    )
VAR _secondSundayInMarch =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            _table,
            [Month] = 3
                && [DayOfWeek] = 7
                && [Year] = YEAR ( MAX ( 'Table'[Date] ) )
        )
    )
VAR _firstSundayInNovember =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            _table,
            [Month] = 11
                && [DayOfWeek] = 7
                && [Year] = YEAR ( MAX ( 'Table'[Date] ) )
        )
    )
RETURN
    IF (
        MAX ( [Date] ) >= _secondSundayInMarch
            && MAX ( [Date] ) < _firstSundayInNovember,
        MAX ( [Date] ) + TIME ( 5, 0, 0 ),
        MAX ( [Date] ) + TIME ( 6, 0, 0 )
    )

Best Regards

[Powerbi_user26]

Hi @v-yiruan-msft ,

I have used your dax and it works, but it is picking only the first sunday of march and first sunday in november.
It should be second sunday in March.

MEASURE =

VAR _table =

    ADDCOLUMNS (

        ALL ( 'Table'[Date]),

        "DayOfWeek", WEEKDAY ( 'Table'[Date], 2 ),

        "Month", MONTH ('Table'[Date] ),

        "Year", YEAR ( 'Table'[Date] )

    )

VAR _secondSundayInMarch =

 CALCULATE(

        MIN('Table'[Date]),

          FILTER (

            _table,

            [Month] = 3

                && [DayOfWeek] = 7

                && [Year] = YEAR ( MAX (OMTLiveTask[ScheduledStartDateTime] ) )

        )

    )

VAR _firstSundayInNovember =

    CALCULATE (

        MIN ( 'Table'[Date] ),

        FILTER (

            _table,

            [Month] = 11

                && [DayOfWeek] = 7

                && [Year] = YEAR ( MAX ( OMTLiveTask[ScheduledStartDateTime] ) )

        )

    )

RETURN

    IF (

        MAX (OMTLiveTask[ScheduledStartDateTime]) >= _secondSundayInMarch

            && MAX ( OMTLiveTask[ScheduledStartDateTime] ) < _firstSundayInNovember,

        MAX ( OMTLiveTask[ScheduledStartDateTime] ) + TIME ( 5, 0, 0 ),

        MAX ( OMTLiveTask[ScheduledStartDateTime] ) + TIME ( 6, 0, 0 )

    )

Anyone can help me in getting the second sunday of the march from the above code.

Great Thanks!

[v-yiruan-msft · Accepted solution]

Hi @Powerbi_user26 ,

Please update the formula of measure as below and check if it can return the expected result...

MEASURE =
VAR _table =
    ADDCOLUMNS (
        ALL ( 'Table'[Date] ),
        "DayOfWeek", WEEKDAY ( 'Table'[Date], 2 ),
        "Month", MONTH ( 'Table'[Date] ),
        "Year", YEAR ( 'Table'[Date] )
    )
VAR _year =
    YEAR ( MAX ( OMTLiveTask[ScheduledStartDateTime] ) )
VAR _marchfirst =
    DATE ( _year, 3, 1 )
VAR _firstsunday =
    _marchfirst - WEEKDAY ( _marchfirst, 2 ) + 7
VAR _secondSundayInMarch = _firstsunday + 7
VAR _firstSundayInNovember =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( _table, [Month] = 11 && [DayOfWeek] = 7 && [Year] = _year )
    )
RETURN
    IF (
        MAX ( OMTLiveTask[ScheduledStartDateTime] ) >= _secondSundayInMarch
            && MAX ( OMTLiveTask[ScheduledStartDateTime] ) < _firstSundayInNovember,
        MAX ( OMTLiveTask[ScheduledStartDateTime] ) + TIME ( 5, 0, 0 ),
        MAX ( OMTLiveTask[ScheduledStartDateTime] ) + TIME ( 6, 0, 0 )
    )

Best Regards

[Nivi_N_26]

Thank you so much it worked