Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
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)
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
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.
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
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.
Hello guys,
you can download the code on my GitHub: https://github.com/nolockcz/PowerQuery/tree/master/The%20Last%20Timestamp%20of%20Loading%20Data
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |