Time Zone Issues in Power BI Desktop with Relative Date Slicer: EST Instead of UTC

[Lexignot]

Hello,

In my PowerBi Desktop report, my relative date time slicer displays dates in UTC time instead of EST, where I am located. The dates used in this slicer have been created with the following query (see below). I have tried both columns from my query in my date slicer (i.e. "Date" and "Date UTC relative", the latter using UTC time as a reference) but it still displays in UTC time locally on my desktop and also on PowerBi Service. My Windows and laptop are properly configured in EST time.

Do you have any ideas on how to fix this? 

This Day displays 6/11/2024 at 9pm on 6/10/2024 EST in my report on PowerBi desktop and PowerBi Service.

Query to define the Date Table:

let
// Define the summer and winter time dates
SummerTimeStartDate = Date.StartOfWeek(#date(Date.Year(DateTime.LocalNow()), 3, 14), Day.Sunday),
WinterTimeStartDate = Date.StartOfWeek(#date(Date.Year(DateTime.LocalNow()), 11, 7), Day.Sunday),

// Define the start and end dates for the calendar as datetime
StartDate = #datetime(2024, 1, 1, 0, 0, 0),
EndDate = #datetime(2026, 12, 31, 23, 59, 59),

// Generate the list of dates as datetime values at midnight
DateList = List.Transform({0..Duration.Days(Duration.From(EndDate - StartDate))}, each Date.AddDays(StartDate, _)),

// Convert the list of dates to a table
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

// Change the type of the Date column to datetimezone
#"Changed Type" = Table.TransformColumnTypes(DateTable,{{"Date", type datetimezone}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date", "Date - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Date - Copy", "Date UTC"}}),

// Remove the timezone from the Date UTC column and adjust the time zone based on the comparison
AdjustedDateTable = Table.TransformColumns(#"Renamed Columns", {{"Date UTC", each DateTimeZone.SwitchZone(_, if DateTimeZone.RemoveZone(_) < SummerTimeStartDate & #time(2, 0, 0) or DateTimeZone.RemoveZone(_) > WinterTimeStartDate & #time(2, 0, 0) then -5 else -4, 0), type datetimezone}}),
#"Changed Type1" = Table.TransformColumnTypes(AdjustedDateTable,{{"Date UTC", type datetime}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Date UTC", "Date UTC relative"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date UTC relative", type date}, {"Date", type date}})
in
#"Changed Type2"

[Anonymous]

Hi @Lexignot ,
According to your description, UTC standard time is used on the power bi service. So if you want to use to EST, you can use the DateTimeZone.SwitchZone function to convert this data to time zone. This way the pwoer bi service will show the US Eastern Time. The following is an example of switching the time zone and displaying it on the pwoer bi service as required

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFSK1QEzjGAMYxjDRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "UTC Now", each DateTimeZone.UtcNow()),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "UTC EST", each DateTimeZone.SwitchZone([UTC Now],-4))
in
    #"Added Custom1"

Final output

DateTimeZone.SwitchZone - PowerQuery M | Microsoft Learn

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Lexignot]

Hi @V-V ,

Thanks for your message. I am not sure I get it. In your answer you converted UTC Now to UTC -4 (i.e. EST). What about for each date in a given year for example? 

See below, I have tried your approach but I still have the same issue with the Date Slicer. Thanks in advance for your help.

https://we.tl/t-3cw4PQCFwI