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 inst...
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
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