Report Refresh Time always Wrong

[ArchStanton]

I wasn't sure where to post this so apologies if its in the wrong place.

I have a following Report Refresh time on my report and today it is showing a time that is exactly 2hrs earlier than the correct time.
I am based in London UK, how do I get the time to align?

let
    Source = DateTime.LocalNow(),
    #"Add 1hr" = DateTime.AddZone(Source,1),
    #"Converted to Table" = #table(1, {{#"Add 1hr"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateTime"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateTime", type datetime}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "DateTime", "DateTime - Copy"),
    #"Extracted Date" = Table.TransformColumns(#"Duplicated Column",{{"DateTime - Copy", DateTime.Date, type date}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Date", "DateTime", "DateTime - Copy.1"),
    #"Extracted Time" = Table.TransformColumns(#"Duplicated Column1",{{"DateTime - Copy.1", DateTime.Time, type time}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Extracted Time",{{"DateTime - Copy", "Date"}, {"DateTime - Copy.1", "Time"}})
in
    #"Renamed Columns1"

I originally added 1 hour before British Summer Time ended in 2025, that was also incorrect, now its 2 hours out.
I'd be grateful for a fix to this because it causes a lot of confusion!

[lbendlin]

The Power BI Service always works in UTC, and so should you.

[ArchStanton]

please see my response to the other SuperUser

[cengizhanarslan]

Try using this instead:

let
    Source           = DateTimeZone.UtcNow(),
    UKTime           = DateTimeZone.SwitchZone( Source, 0, 0 ),
    UKAdjusted       = DateTimeZone.ToLocal( Source ),
    AsDateTime       = DateTimeZone.RemoveZone( UKAdjusted ),
    AsTable          = #table( 1, {{ AsDateTime }} ),
    RenamedCols      = Table.RenameColumns( AsTable, {{"Column1", "DateTime"}} ),
    ChangedType      = Table.TransformColumnTypes( RenamedCols, {{"DateTime", type datetime}} ),
    WithDate         = Table.AddColumn( ChangedType, "Date", each DateTime.Date( [DateTime] ), type date ),
    WithTime         = Table.AddColumn( WithDate, "Time", each DateTime.Time( [DateTime] ), type time )
in
    WithTime

[ArchStanton]

I've just tested your code and there's still an hour difference, the true refresh time that I can see in Service is 10:01 but the report sows 09:01 so its minus -1hr. 

Is there a plus +1hr we can apply somewhere to your code?

[v-ssriganesh]

Hi @ArchStanton,
Thank you for posting your query in the Microsoft Fabric Community Forum.

Adding a manual +1 hour isn’t a reliable fix here. The underlying issue is that Power BI / Fabric Service evaluates current date‑time functions using UTC/server behavior, and functions like DateTimeZone.ToLocal() don’t necessarily return UK local time (BST/GMT) when they run in the Service.

May be that’s why you’re currently seeing the 1 hour difference. the query result is still based on UTC logic. Manually adding an hour may look correct now, but it will typically become incorrect again when daylight‑saving time changes.

For your reference: Local, fixed, and UTC variants of current time functions - PowerQuery M | Microsoft Learn

• The Service refresh time (10:01) is the actual refresh completion time.
• The report timestamp is being generated separately from query logic.
• A fixed +1 hour offset is only temporary.

Use DateTimeZone.UtcNow() as the base value and apply explicit UK BST/GMT daylight‑saving logic, rather than relying on “local” conversions or hard‑coded offsets.

Best regards,
Ganesh Singamshetty.

[ArchStanton]

Thank you for the detailed explanation, that all makes sense.

I understand that it is only a temporary fix but where do I add an hour to the code below?

let
    Source           = DateTimeZone.UtcNow(),
    UKTime           = DateTimeZone.SwitchZone( Source, 0, 0 ),
    UKAdjusted       = DateTimeZone.ToLocal( Source ),
    AsDateTime       = DateTimeZone.RemoveZone( UKAdjusted ),
    AsTable          = #table( 1, {{ AsDateTime }} ),
    RenamedCols      = Table.RenameColumns( AsTable, {{"Column1", "DateTime"}} ),
    ChangedType      = Table.TransformColumnTypes( RenamedCols, {{"DateTime", type datetime}} ),
    WithDate         = Table.AddColumn( ChangedType, "Date", each DateTime.Date( [DateTime] ), type date ),
    WithTime         = Table.AddColumn( WithDate, "Time", each DateTime.Time( [DateTime] ), type time )
in
    WithTime

Or, i can add it to my measure like this

LastRefreshDate = "Last Refresh Date: " & MAX(ReportRefreshDateTime[DateTime])+1

I will persevere with this option I only have to changed twice a year.
Thank you

[v-ssriganesh]

Hello @ArchStanton,

The cleanest place to add the extra hour is after the timezone has been removed and the value becomes a standard datetime.

Specifically, add the following step after AsDateTime:

AdjustedDateTime = AsDateTime + #duration(0, 1, 0, 0)

Then use AdjustedDateTime when creating the table instead of AsDateTime.

This will shift the displayed time by +1 hour and should align it with what you are currently seeing in the Service.

Best regards,
Ganesh Singamshetty.

[jjustjjo]

Hi @v-ssriganesh  Not sure why you at-d me.  perhaps you should check who you meant to reply to?

[ArchStanton]

Thanks for getting back to me, I'm not the best with Power Query and I'm getting this error message when I add your step:

[v-ssriganesh · Accepted solution]

Hello @ArchStanton,
Thank you for sharing the screenshot.

The error occurs because in Power Query, each line must be added as a new step inside the let in block, with the previous step ending in a comma. If the new line is entered by itself in the formula bar, Power Query tries to evaluate AdjustedDateTime before it exists.

Please update your query like this Add the new step after AsDateTime:

let

    Source           = DateTimeZone.UtcNow(),

    UKTime           = DateTimeZone.SwitchZone(Source, 0, 0),

    UKAdjusted       = DateTimeZone.ToLocal(Source),

    AsDateTime       = DateTimeZone.RemoveZone(UKAdjusted),

    AdjustedDateTime = AsDateTime + #duration(0, 1, 0, 0),



    AsTable          = #table(1, {{AdjustedDateTime}}),

    RenamedCols      = Table.RenameColumns(AsTable, {{"Column1", "DateTime"}}),

    ChangedType      = Table.TransformColumnTypes(RenamedCols, {{"DateTime", type datetime}}),

    WithDate         = Table.AddColumn(ChangedType, "Date", each DateTime.Date([DateTime]), type date),

    WithTime         = Table.AddColumn(WithDate, "Time", each DateTime.Time([DateTime]), type time)

in

    WithTime

Power Query steps work sequentially, so the new step must be part of the query and referenced later in AsTable.

Please try the above structure and let me know how it goes.

Best regards,
Ganesh Singamshetty

[ArchStanton]

My bad, I tried to enter the line in the Query Steps section and not in the Advanced editor window.

Sorry for the confusion!
Thanks again for your help

[ArchStanton]

Thanks for this, I've just updated it with your code - lets see what happens.

Last night, after the edit that I made which I mentioned above, the report said it refreshed at 23:55:

but the actual refreshed time in service says 02:56 despite me having it scheduled for 01:30:

Scheduled refresh time:

Its such a mess, nothing is aligned, hopefully your fix will work, I'll try it to today.

[ArchStanton]

By trying different combinations I think I've fixed this - I will see what happens overnight before I close the ticket: