In this blog post, I’ll make an update to the Last Refresh Date and Time blog post where I showed you how to insert the last refresh into your Power BI reports so you or your clients can see it. I’ve done a little research on this to try and make things simpler. You may watch the full video of this tutorial at the bottom of this blog.
This way, you don’t have to go in each time and change the number so that you can update to the last refresh. For example, I live in the Eastern time zone, which is -4 hours, but now that we have moved the clocks back, I am now -5 hours from UTC time. I’ve come up with another solution so that you don’t even have to adjust the number.
Previous Solution To Get The Last Refresh Date In Power BI
Let’s head on over to the query editor to see how things were set up before. The Last Refresh Version 2 is the one that we created last time.
If we open up the advanced editor, we’ll see the M code from before. In the last line of code, I had the last refresh at -4 to get the correct time for my time zone, which is the Eastern time zone compared to UTC time.
Well, we can create something better. So let’s go ahead and create another last refresh table with a new code. I’ll exit and create a new blank query.
And then I’ll go to the Advanced Editor and then copy and paste the code in there.
As you can see, the highlighted line right is the column that creates the last refresh date and time where I don’t have any numbers involved.
Last Refresh Date & Time: Updated Solution
I’ll just name this one Last Refresh Version 3.
With this new M code that I entered, it automatically marked this as -5 hours, which is the current time zone that I reside in now.
Whenever the time changes with this new M code, I do not have to go in and adjust the time like I did before.
Let’s go to Added Custom and double click, so you can see how we’re taking the last refresh time minus the duration.
You need to be aware that if you build reports for yourself or your company that are in the same time zone, you need to keep this same format.
But for those of you like myself that build Power BI reports for clients throughout the world, we go over different time zones. What I do for these clients is change this second zero right here.
If I change this second zero to 1, it will give me the time right now in the central time zone.
If I were to go back into that column and enter 2, it will give me mountain standard time.
And if I enter 3, that will give me Pacific standard time.
By doing this, every time the time changes you won’t have to worry about going into each one of your reports and updating the last refresh date time. This is a nice technique to skirt around that refresh date/time.
I’ll now go back into that column and change it back to a 0 to keep it in Eastern standard time.
I will then copy and paste this.
For the second table, let’s use the Last Refresh Version 3 and choose that last refresh date and time column. And now we can see that this is the current date and time for Eastern standard time.
***** Related Links *****
Show Last Refresh Date/Time In Your Power BI Reports
Power BI Custom Visuals – Build A Reporting Application
Power BI Custom Visual – Creating Text Summaries
Conclusion
This technique did everything that we wanted to do and going forward, we won’t have to go in and update the last refresh date & time in our Power BI reports.
I hope you enjoyed this tidbit to help improve your last refresh date & time in all of your reports. If you haven’t already subscribed to the Enterprise DNA channel, please make sure you do to get all the incoming content.
All the best,
Jarrett
