Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nivi_N_26
Regular Visitor

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

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.

1 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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you so much it worked

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.