Help with power query

[svishwanathan]

Hello

I would like to identify if a date falls within DST (daylight saving) or not.

This works fine in excel

IF(AND(SysDate[SysDate]>=DATE(YEAR(SysDate[SysDate]),3,1)+14-WEEKDAY(DATE(YEAR(SysDate[SysDate]),3,1)-1), SysDate[SysDate] < DATE(YEAR(SysDate[SysDate]),11,1)+7-WEEKDAY(DATE(YEAR(SysDate[SysDate]),11,1)-1)),"DST","No")

Can anyone please help me to write this in power query

I know that the commas need to go and I need to put then and els

But I think I cannot use the syntax I am using to return the date for the second sunday in march and 1st sunday in november

Regards

Swati

[Eric_Zhang · Accepted solution]

---

@svishwanathan wrote:

Hello

I would like to identify if a date falls within DST (daylight saving) or not.

This works fine in excel

IF(AND(SysDate[SysDate]>=DATE(YEAR(SysDate[SysDate]),3,1)+14-WEEKDAY(DATE(YEAR(SysDate[SysDate]),3,1)-1), SysDate[SysDate] < DATE(YEAR(SysDate[SysDate]),11,1)+7-WEEKDAY(DATE(YEAR(SysDate[SysDate]),11,1)-1)),"DST","No")

Can anyone please help me to write this in power query

I know that the commas need to go and I need to put then and els

But I think I cannot use the syntax I am using to return the date for the second sunday in march and 1st sunday in november

Regards

Swati

---

@svishwanathan

It doesn't have to use Power Query, in DAX, the formula is the same. Any reason why not using DAX?

dstDayOrNot = IF(
    AND(
        SysDate[SysDate] >=
        DATE(
            YEAR(
                SysDate[SysDate]
            ),
            3,
            1
        ) + 14 -
        WEEKDAY(
            DATE(
                YEAR(
                    SysDate[SysDate]
                ),
                3,
                1
            ) - 1
        ),
        SysDate[SysDate] <
        DATE(
            YEAR(
                SysDate[SysDate]
            ),
            11,
            1
        ) + 7 -
        WEEKDAY(
            DATE(
                YEAR(
                    SysDate[SysDate]
                ),
                11,
                1
            ) - 1
        )
    ),
    "DST",
    "No"
)