cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bestmbaman Member
Member

Showing last refreshed date that user pushed the refresh button

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

1 ACCEPTED SOLUTION

Accepted Solutions
bestmbaman Member
Member

Re: Showing last refreshed date that user pushed the refresh button

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)

View solution in original post

5 REPLIES 5
Super User II
Super User II

Re: Showing last refreshed date that user pushed the refresh button

Hi @bestmbaman,

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.

 

Capture.PNG

bestmbaman Member
Member

Re: Showing last refreshed date that user pushed the refresh button

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

Super User II
Super User II

Re: Showing last refreshed date that user pushed the refresh button

Hi @bestmbaman,

 

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.

Highlighted
campagnol54 Regular Visitor
Regular Visitor

Re: Showing last refreshed date that user pushed the refresh button

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

bestmbaman Member
Member

Re: Showing last refreshed date that user pushed the refresh button

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)

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors