Forum Discussion

DMB90's avatar
DMB90
Icon for Helper II rankHelper II
1 year ago
Solved

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

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.

 

Need Help
  • jgeddes's avatar
    jgeddes
    1 year ago

    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

1 Reply

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    1 year ago

    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