cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User

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
Super User

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.

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
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)