Forum Discussion

Virtual_Ames's avatar
Virtual_Ames
Microsoft Employee
10 years ago
Solved

Easy consistent Last Refresh Time (your local time zone) solution - no matter how refreshed

If you want to make sure your viewers know how fresh the data in a report is - you may want to have a "Last Refreshed" tile showing.  The challenge is - if you publish from your desktop, you get last...
Show and Tell
Tips and Tricks
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    Virtual_Ames

     

    Thanks for your sharing. That's a awesome idea to indicate a precise refresh time. :)

     

    Just some tips, no need to import the web as a source, the Power Query,also known as "M", has a "DateTimeZone.UtcNow()", you can get UTC time with that function and tweak different timezones based on UTC.

Rickmaurinus's avatar
Rickmaurinus
Helper V
4 years ago

As an easy solution, you can adjust for daylight saving time. You start with the UTC time, and adjust the TimeZone offset in the code. 

 

 

let
  UTC_DateTimeZone = DateTimeZone.UtcNow(),
  UTC_Date         = Date.From(UTC_DateTimeZone),
  StartSummerTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday),
  StartWinterTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday),
  UTC_Offset       = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 2 else 1,
  CET_Timezone     = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)
in
  CET_Timezone

 

 

I explained a bit about these concepts here:

Fix Last Refresh Date/Time in Power BI (Incl Daylight Savings) - Gorilla BI

 

Enjoy!

 

--------------------------------------------------

@ me in replies or I'll lose your thread

 

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    This worked like a charm! 👍