Hi. All of my reports contain a simple refresh timestamp using DateTime.LocalNow(). This is no longer giving me the desired result since DST happened in the UK. If I refresh a report on my desktop then the timestamp is correct, i.e. the current date/time in the UK. If I refresh on the server then it's 1 hour behind, which is techincally correct as the server is not in the UK, however it's not what I want. I've tried several other DateTime options, but can't get my desired result, which is always to show the current UK time. What is the correct DateTime option to achieve this?

V-yubandi-msft Thanks for code. It didn't work but I used it as the basis for a solution that has fix the problem for me. = let// Use FixedUtcNow for a stable “refresh moment” during evaluationutcNow = DateTimeZone.FixedUtcNow(),year = Date.Year(DateTimeZone.RemoveZone(utcNow)),// UK DST rules: BST starts 01:00 UTC last Sunday in March// BST ends 01:00 UTC last Sunday in OctoberlastSundayMarch = Date.StartOfWeek(#date(year, 3, 31), Day.Sunday),lastSundayOct = Date.StartOfWeek(#date(year,10, 31), Day.Sunday),bstStartUtc = #datetimezone(year, 3, Date.Day(lastSundayMarch), 1, 0, 0, 0, 0),bstEndUtc = #datetimezone(year,10, Date.Day(lastSundayOct), 1, 0, 0, 0, 0),isBST = utcNow >= bstStartUtc and utcNow < bstEndUtc,offset = if isBST then 1 else 0,ukNowZoned = DateTimeZone.SwitchZone(utcNow, offset),ukNow = DateTimeZone.RemoveZone(ukNowZoned)inukNow

DateTime Options to Always Obtain Current UK Time

7 Replies

MJG2112
Advocate II
5 months ago
V-yubandi-msft Thanks for code. It didn't work but I used it as the basis for a solution that has fix the problem for me.

= let
// Use FixedUtcNow for a stable “refresh moment” during evaluation
utcNow = DateTimeZone.FixedUtcNow(),
year = Date.Year(DateTimeZone.RemoveZone(utcNow)),
// UK DST rules: BST starts 01:00 UTC last Sunday in March
// BST ends 01:00 UTC last Sunday in October
lastSundayMarch = Date.StartOfWeek(#date(year, 3, 31), Day.Sunday),
lastSundayOct = Date.StartOfWeek(#date(year,10, 31), Day.Sunday),
bstStartUtc = #datetimezone(year, 3, Date.Day(lastSundayMarch), 1, 0, 0, 0, 0),
bstEndUtc = #datetimezone(year,10, Date.Day(lastSundayOct), 1, 0, 0, 0, 0),
isBST = utcNow >= bstStartUtc and utcNow < bstEndUtc,
offset = if isBST then 1 else 0,
ukNowZoned = DateTimeZone.SwitchZone(utcNow, offset),
ukNow = DateTimeZone.RemoveZone(ukNowZoned)
in
ukNow
pankajnamekar25
Super User
5 months ago
Hello MJG2112

Power BI Service uses UTC, not UK time. To always show UK time, use UtcNow() and adjust for DST manually or via logic

To always get correct UK time in Microsoft Power BI, use UTC and convert it to UK time with DST logic:

use this Mcode
DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 0)

OR

Handle it dynamically using logic based on date (last Sunday of March to October)

Simple practical fix most people use

DateTimeZone.UtcNow() + #duration(0,1,0,0)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.

Thanks,

Connect with me on:
LinkedIn |
Data With Pankaj - YouTube
InsightsByV
Super User
5 months ago
Hi,

If this is just a simple refresh timestamp, you can add below M-code in blank query and use LastRefresh, it will fix your problem:

let
UK_TimeZone = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 0),
UK_Time = DateTimeZone.ToLocal(DateTimeZone.UtcNow()),
UK_Text = DateTime.ToText(UK_Time, "dd-MMM-yyyy hh:mm tt", "en-GB"),
#"Converted to Table" = #table(1, {{UK_Text}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "LastRefresh"}})
in
#"Renamed Columns"
Lodha_Jaydeep
Solution Sage
5 months ago
Hi MJG2112 ,

Great question! This is a very common issue after DST changes. The problem is that DateTime.LocalNow() picks up the local time of wherever the code runs your desktop in the UK during development, but the Power BI service servers which run on **UTC** during scheduled refreshes.
The solution is to use 'DateTimeZone.UtcNow()' and manually apply the UK offset, so the result is always consistent regardless of where the refresh runs.
**Option 1 Simple Fix (BST only, UTC+1):**
'''
DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 1))
'''
This always adds 1 hour, so it works during BST (summer) but will be 1 hour ahead during GMT (winter).
**Option 2 Fully Dynamic UK Time (Recommended):**
This automatically switches between GMT (UTC+0) and BST (UTC+1) based on the date:
'''
DateTime.From(
DateTimeZone.SwitchZone(
DateTimeZone.UtcNow(),
if DateTimeZone.UtcNow() >= #datetimezone(Date.Year(DateTime.LocalNow()), 3, 31, 1, 0, 0, 0, 0)
and DateTimeZone.UtcNow() < #datetimezone(Date.Year(DateTime.LocalNow()), 10, 27, 1, 0, 0, 0, 0)
then 1 else 0
)
)
'''
This checks whether the current UTC date falls within the BST window (last Sunday of March to last Sunday of October) and applies the correct offset accordingly.
Option 2 is the most robust solution and will handle DST transitions automatically every year without any manual changes.
Hope this helps! Let us know if you have any questions. Please consider this as an accecpted solution if it's helpful. Kudos will be really appriciated.
- MJG2112
  Advocate II
  5 months ago
  Hi Lodha_Jaydeep I think I'm missing something. I'm still seeing the time as 1 hour behind UK time in the service. Do have I to take the result of the IF condition (i.e. 1 or 0) and add it to the DateTime.LocalNow() ?
  - V-yubandi-msft
    Community Support
    5 months ago
    Hi MJG2112 ,
    You don't need to add the IF result to DateTime.LocalNow(). The main problem is that LocalNow() relies on the server time in the Service, so it may not always give you UK time. Instead, use DateTimeZone.UtcNow() and apply the offset from the IF condition with SwitchZone.
    Try This,
    
    let utcNow = DateTimeZone.UtcNow(), year = Date.Year(DateTimeZone.RemoveZone(utcNow)), startBST = Date.StartOfWeek(#date(year, 3, 31), Day.Sunday) + #time(1,0,0), endBST = Date.StartOfWeek(#date(year, 10, 31), Day.Sunday) + #time(1,0,0), offset = if utcNow >= DateTimeZone.From(startBST) and utcNow < DateTimeZone.From(endBST) then 1 else 0, UKTime = DateTimeZone.SwitchZone(utcNow, offset) in DateTime.From(UKTime)
    
    This should handle both GMT (UTC+0) and BST (UTC+1) correctly for Desktop and Service.
    
    This method works for most cases could you try it out and see if it meets your needs?
    
    If you continue to notice any discrepancies, as Lodha_Jaydeep mentioned, please share the specific query or measure you are using so we can assist in identifying the issue.
    
    Thank You.