Get datetime in CST in local and workspace

[spartanboy]

Hi,

I need to show report refresh datetime in CST zone on local machine as well as on workspace. Below M code to get UTC datetime and convert to CST.

Local machine is always shows in CST, but on publishing the repor to workspace get conveted to UTC

let
    // Get the current UTC datetime
    Source = DateTimeZone.UtcNow(),
    
    // Convert the UTC datetime to CST by subtracting 6 hours (CST is UTC-6)
    CSTDateTime = DateTimeZone.SwitchZone(Source, -6),
    
    // Convert the datetime to text with CST label
    CSTDateTimeText = DateTime.ToText(DateTime.From(CSTDateTime), "yyyy-MM-dd HH:mm:ss") & " CST",
    
    // Convert the result to a table
    #"Converted to Table" = #table(1, {{CSTDateTimeText}}),
    
    // Rename the column to "DateTime"
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "DateTime"}})
in
    #"Renamed Columns"

[aj1973 · Accepted solution]

Please try this code and adjust the number '6' below according to your Pwer bi service time zone

let
// Get the current UTC datetime
Source = DateTime.FixedLocalNow() - #duration(0,6,0,0),

#"Converted to Table" = #table(1, {{Source}}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "DateTime Refresh"}})
in
#"Renamed Columns"

[aj1973]

Hi @spartanboy 

Timezones of local machines and power BI service might be different. Try using 
CurrentDateTime = DateTime.LocalNow() and then subtract number of hours according to the time rendered by the service.

[spartanboy]

by doing the same i still have the same issue. can you please provide an example?

[aj1973 · Accepted solution]

You can also add this to your code as well Source = DateTimeZone.UtcNow() - #duration(0,6,0,0),

here is the result in service

[aj1973 · Accepted solution]

Please try this code and adjust the number '6' below according to your Pwer bi service time zone

let
// Get the current UTC datetime
Source = DateTime.FixedLocalNow() - #duration(0,6,0,0),

#"Converted to Table" = #table(1, {{Source}}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "DateTime Refresh"}})
in
#"Renamed Columns"