Showing last refreshed date that user pushed the refresh button

[Anonymous]

Hi gurus,

I have a question in regards to my situation:

I have a report consisting of 10 gauges. My report has several dim and fact tables. One of the tables gets refreshed by user pushing refresh button in PBI. Before doing it, he receives the updated Excel file and writes over the same file (like everyday morning) from yesterday. Now, I used the following formula and add it to the fact table to be able to capture the latest refresh date when the user pushes the refresh button in PBI and placed a card on top of my gauges to visualize the date:

#table(type table[Last Refresh=datetime], {{DateTime.LocalNow()}})

the problem is this only shows me Today’s date which could be easily obtained by:

Todate = Today()

But what I need is not to show just today’s date but the real time that the user refreshes data. In other words if the user just opens up the report for 2 weeks ago or 2 days ago, It should show the same date not today’s date!

What would be the DAX formula for it?

P.S. I already reviewed this topic but it doesn't address my issue:

https://community.powerbi.com/t5/Service/last-time-refresh/td-p/7200

Many thanks

[Anonymous · Accepted solution]

The Problem solved by using the

#table(type table[Last Refresh=datetime], {{DateTime.LocalNow()}})

and adding a custom column in my facts table which works with

= Table. AddColumn(#Added Custom1, "Date", each DateTime.Date([LocalRefreshDate]), Type Date)

[campagnol54]

Hello,

Here is the solution I use without any problem :

https://thebipower.fr/index.php/2019/01/25/add-a-refresh-date-to-your-report/

Hoping it will work for you

Regards

[Nolock]

Hi @Anonymous,

the PowerQuery query looks fine, I'd say the problem is only the graphical output. In Power Bi Desktop, go to Modelling / Format / Date Time and choose your expected format.

[Anonymous]

Thanks @Nolock ,

I don't have any problem with showing date and time because I changed the format in modeling to show just date! Bu my problem is everytime I open the file BEFORE HITTING THE REFRESH BUTTON it shows me the today's date while it shouldn't be today since I haven't refreshed it yet. What is the solution now?

Thanks Gurus for your replies

[Nolock]

Hi @Anonymous,

it should work, if you use the following PowerQuery query.

let
    Source = #table(type table[Last Refresh=datetime], {{DateTime.LocalNow()}})
in
    Source

When you click on Refresh, it stores a timestamp in the local database and it stays there until you click on Refresh again.

Please download the following pbix file to check the solution: Last Refresh Timestamp.

[Syndicate_Admin]

@Nolock Can I please get access to Last Refresh TimeStamp file?

[Nolock]

Hello guys,

you can download the code on my GitHub: https://github.com/nolockcz/PowerQuery/tree/master/The%20Last%20Timestamp%20of%20Loading%20Data 

[learner03]

@Nolock Can I please get access to Last Refresh TimeStamp file?