Not looking for help - trying to help.
As many in this forum I have been looking for a way to show the correct Last refreshed datetime in my reports published to the Power BI Service.
I am located in Denmark and we have Daylight Saving Time and when updating my data in the Power BI Desktop the datetime showed the correct data. But alas, when refreshing the same report once published to the Power BI service suddenly showed UTC timezone.
I look at different solutions and ended up with a simple solution that I hope others can use.
In Denmark we switch the time the last Sunday in March and October.
Solution:
1. Create a blank query and open advanced query
2. Copy/paste the code below and voila
You can adjust the dates for the DST switch and +/- hours to suit your timezone.
let
Source = Table.FromRows({{Date.Year(DateTime.LocalNow())}},{"Year"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
#"DST Start Date" = Table.AddColumn(#"Changed Type", "DST StartDate", each Date.AddDays(#date([Year],3,31),Date.DayOfWeek(#date([Year],3,31), Day.Sunday)*-1)),
#"DST End Date" = Table.AddColumn(#"DST Start Date", "DST EndDate", each Date.AddDays(#date([Year],10,31),Date.DayOfWeek(#date([Year],10,31), Day.Sunday)*-1)),
#"Last Refreshed UTC" = Table.AddColumn(#"DST End Date", "Last Refreshed (UTC)", each DateTimeZone.RemoveZone(DateTimeZone.UtcNow())),
#"Last Refreshed" = Table.AddColumn(#"Last Refreshed UTC", "Last refreshed", each if DateTime.Date([#"Last Refreshed (UTC)"]) > [DST StartDate] and DateTime.Date([#"Last Refreshed (UTC)"]) < [DST EndDate] then [#"Last Refreshed (UTC)"] + #duration(0,2,0,0) else [#"Last Refreshed (UTC)"] + #duration(0,1,0,0)),
#"Removed Columns" = Table.RemoveColumns(#"Last Refreshed",{"Year", "DST StartDate", "DST EndDate", "Last Refreshed (UTC)"})
in
#"Removed Columns"
