Last Refresh Date in Power BI

Advocate II

1 year ago

One thing to be aware of with using the DateTime.LocalNow function is that it can give different results depending on things.

For reports you are testing in Power BI desktop, it'll work great as it's pulling the date/time from your computer. However, once you publish the report, the date/time is pulled from the server and not your location. If the server is in a different time zone you will get a different result. I discovered this when my report was showing it was being refreshed about 4 hours ahead of the my time.

However, you can adjust the code to account for this. Here is code I have used. I wish I could give credit as to who created it as I found it off the web. And, it would can be tailored to account for Daily Savings Time (and the section may have to be updated as well)
***************

let

date = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-4,0)),

time = DateTime.Time(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-4,0)),

//negative four (-4) is based off of EST adjust this on date and time to your timezone.

firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),

SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday),

isSummerTime = (date = SecondSundayOfMarch and time >= #time(1,0,0))

(date > SecondSundayOfMarch and date < firstSundayOfNovember)

(date = firstSundayOfNovember and time >= #time(1,0,0)),

timeZone = (Number.From(isSummerTime))*1 - 1,

//negative one (-1) may need to be adjusted depending on your timezone.

ltime =

DateTime.From(date)

+ #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))

+ #duration(0, timeZone, 0, 0)

ltime

Blog Post