Automatic refresh not appearing on Last refresh card

[4jhill]

On PowerBi desktop, I have set up a Last Refresh card, that displays data and time of this. I know that when I hit "refresh" manually on PowerBi desktop that the date and/ or time changes. I set an automatic refresh on PowerBi web service (by clicking on the scedule refresh that appears when you hover over the semantic model - see below) which automatically refreshed at 0300 as requested. 

However, when I click into the report the 'last refresh' card does to reflect the automatic refresh. Does anyone know how I can amend this "last refresh" card so it actually reflects the automatic last refresh date/time. 

Due to my data source being an online sharepoint list, I cannot set the automatic refresh through PowerBi Desktop. 

[v-veshwara-msft]

Hi @4jhill ,
We haven't heard from you.
Does the solutions met your need?. If yes, could you consider accepting as solution to help others benefit.
If not please reach out.

Thank you.

[v-veshwara-msft · Accepted solution]

Hi @4jhill ,
Thank you for your question, and great points raised by @Tutu_in_YYC  and @tharunkumarRTK .
It's true that ensuring the query is included in the dataset refresh and refreshing the browser after a scheduled refresh are important steps to check.
In addition to that, I’d like to share a solution that ensures the 'Last Refresh' card reflects the automatic refresh.
>Modify the query as below
 let
    CurrentUTCDateTime = DateTimeZone.UtcNow(),
    CurrentLocalDateTime = DateTimeZone.SwitchZone(CurrentUTCDateTime, 2),
    LocalDateTime = DateTimeZone.RemoveZone(CurrentLocalDateTime) // Remove the time zone metadata for consistency
in
    LocalDateTime
Now again publish the report to the service and configure the schedule refresh.
This time it should work and display the correct date/time in the card.

Hope this helps. Please accept as solution so that other users can benefit from it. Kudos would be appreciated.
Best Regards.

[4jhill]

Thank you very much. I just adjusted this to my time zone and it worked. 

[tharunkumarRTK]

@4jhill 

From what I understand, you used power query function to capture the refresh time and you also fixed the timezone offset value but still you are not able to see the last refresh datetime.

This sounds odd, I am wondering have you excluded this table from refresh? Just check if you have disabled it. 

Is it happening even with manual refresh? I would suggest you to republish the dataset and check if that helps.

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Tutu_in_YYC]

How did you create the last refresh card? DAX? or Power Query? what is the code? 

Try this query and use this for last refresh:

let
Source = DateTime.FixedLocalNow(),
#"Converted to Table" = #table(1, {{Source}})
in
#"Converted to Table"

 You may have to adjust timezone

[4jhill]

The last refresh query is definetly included in the refresh, I tried refreshing the browser and the code you supplied above but didn't work. Thanks for your answer - much appreciated

[4jhill]

I used Power Query: 

let
Source = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),2)),
#"Converted to Table" = #table(1, {{Source}}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Last refresh date/time"}})
in
#"Renamed Columns"

[Tutu_in_YYC]

yeah this should work just fine. Check 2 things:
1. if the query is included in refresh

2. after the scheduled refresh in power bi service, refresh the browser. Sometimes the browser cache the previous value