Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
4 months ago
Solved

Report Refresh Time always Wrong

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 co...
Need Help
Show and Tell
  • v-ssriganesh's avatar
    v-ssriganesh
    3 months ago

    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's avatar
ArchStanton
Power Participant
4 months ago

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's avatar
v-ssriganesh
Community Support
3 months ago

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's avatar
    ArchStanton
    Power Participant
    3 months ago

    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's avatar
      v-ssriganesh
      Community Support
      3 months ago

      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.

      • ArchStanton's avatar
        ArchStanton
        Power Participant
        3 months ago

        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: