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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors