cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to: Get Last Refreshed Date in local DST time in 5 clicks

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"

 

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
Solution Sage

Hi @Anonymous ,

It's pleasant that you can share your sloution to us, people who meet a similar problem will be very happy with it.

Thanks & Regards,

Teige

View solution in original post

1 REPLY 1
TeigeGao
Solution Sage
Solution Sage

Hi @Anonymous ,

It's pleasant that you can share your sloution to us, people who meet a similar problem will be very happy with it.

Thanks & Regards,

Teige

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.