Adjust Power BI Query Time to be Dynamic with Daylight Savings Begins/Ends Formulas

[DMB90]

I'm attempting to account for Daylight Savings Begins/Ends in my Time Refresher and was successful until I got to this part. I use Pacific Standard Time (United States) so need to subtract hours instead of adding. 

I used this video and was successful until this part at the 13 minute mark. This person is using time outside of the US.

What I think my formula should look like.

Can I possibly edit CurrentDateTime here to be minus, so then I can just add hours to my if CurrentDateTime formula.

[jgeddes · Accepted solution]

DateTimeZone.SwitchZone() will accept negative numbers.
Here is a small example...

let
    spring_forward_utc = #datetimezone(2025,3,9,10,0,0,0,0),
    example_table =
    #table(
        {"dateTimeUTC"},
        {
            {#datetimezone(2025,3,9,09,59,32,0,0)},
            {#datetimezone(2025,3,9,10,00,35,0,0)}
        }
    ),
    change_type = 
    Table.TransformColumnTypes(
        example_table,
        {{"dateTimeUTC", type datetimezone}}
    ),
    add_local = 
    Table.AddColumn(
        change_type, 
        "Local Time", 
        each 
        if [dateTimeUTC] >= spring_forward_utc
            then DateTimeZone.SwitchZone([dateTimeUTC], -7)
            else DateTimeZone.SwitchZone([dateTimeUTC], -8),
        type datetimezone
    )
in
    add_local