Yep, doing this as a common practice in my reports for the same reason as you describe. To show what the last refresh time was to the end user, but also to make it easier to detect refresh failures.
I prefer to have the date shown when my data was last modified. Having a refresh date doesn't tell me much if the data driving it hasn't changed. So, in the query editor, we bring in the folder where the data source file is stored, filter for the correct file, then use the date modified.
This was a great video and helped me a lot. I know nothing about Power Bi but need to teach myself a few things for work tasks. Tried to use Copilot/AI to learn how to do a refresh but couldn’t get it to do what I wanted. Your video solved it quickly and I feel like I understand Power BI just a tiny bit better
Hey Guy in a Cube. You have some great videos! I am just getting my feet wet in PowerBI and these are super useful. On this particular topic, this works great only when you have a published page, the published page uses a completely different timezone than my desktop apps does. Is there a way to FORCE a timezone in that date capture so both the desktop app and the published page keep the same timezone?
Thanks so much. I would say they only thing to improve on would be to change the category label to larger text size, rename table to "refreshed date" and change field from last to first such that it reads " Last Refreshed Date"
Humm, I tried this but as soon as I put my dashboard on the Power Bi Server, it used the date the server was using, which is in a different country. How would you approach this situation?
Thank you from Melbourne Au. Only been using PowerBI about a year and believe I need to show my audience when the data was refreshed. Very easy to follow, thanks again. Mikey Mike
Presuming that you are looking for IST. Adding 5 n half hours to date.localnow() should do the job if you are not worried much about the time displayed in PBI desktop. But I believe a better approach should exist
Our company has locations all over the US so I just report everything in EST and instead of using Adam's approach, I just create a datasource that queries the GETDATE() of our SQL server and return that in our model . Not sure of your datasource but that might help you or others.
Hey Shiva, you can use the following code: Source = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-3,0), where "-3" is the difference between your timezone and UTC timezone.
Followed these instruction exactly and it worked great. What about correcting for time zone? How do I modify the invoked function so my app agrees with the refresh schedule I set in the app.
This is great, except when you have people from across various different time zones looking at a report. The "Local Now" of the server (in our case) is UTC. How do I get that value to translate to the logged in user's time. I'm guessing it's not possible given that when I put a simple measure of SimpleTime=NOW() and I display that as a card, it's coming up as the server's local time. Whereas when I do this on my desktop, the NOW() shows as the local time and UTCNOW() shows the UTC (I know, under redundant see redundant). I have users in all 4 US timezones so I really can't do an offset at this point.
My approach (working with a database of sales data that refreshes overnight) is to instead report the last invoice date from my report. But I will definitely keep this in mind for other applications.
Yup, that is another great way to do it. Depends on what you want to see. When the model was actually last refreshed, regardless of the data inside, or the last current record you have within the data. Or, maybe both :)
If I start the refresh at 10:00:00 and the refresh ends at 11:00:00, the table will have a close value to 10, right? Something like 10:00:02 or so. How could I achieve to get the end time, or something close to it?
Depending on how exact users want to see the last refresh date/time, maybe it's worth finding the average time of a refresh and then add that time average to the current date/time M language function. Just a thought and may not work but it's an option.
Hi @guyinacube, I use a calculated col in my report just create a col and enter UTCNow() function to get the date time in utc, now to show the time zone i just concatenate this function with " UTC" string and now they know when their data was last refreshed.
That is another way to do it. I usually try and push things as far back as I can. But, assuming it is by itself, that may not have a lot of overhead from a calculated column perspective.
@@GuyInACube The issue with that approach happens in the service when an end user hits the refresh button and the DAX calculates but the data isn't actually refreshed. Power Query is the only way outside the metadata in the new app experience
One thing that would be cool is to see how to create a timestamp in your report that shows the last time a Dataflow/set has refreshed, not necessarily the report itself
Hey, thanks for the tip. One problem of publishing this to Power BI Online is that Power BI Online only works on coordinated universal time (UTC). It will change your local time zone whatever you do. So for Europeans with summer/winter time zone it is a pain to work with.
Hey Guys, For those who are struggling with the UTC time of the Refresh inside the Service, a good approach is to use the code below: Source = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-3,0), where "-3" is the difference between your timezone and UTC timezone.
If you do this on the local machine, updated and upload I assume it will work. but will it still work once you publish and updates run on the service and not from you local machine?
What about displaying the NEXT scheduled refresh date. This information is available in the Power BI Service when you have Scheduled Refresh times selected. I'm just wondering if you can display something similar in Power BI Desktop.
Thanks for the video. It is pretty much a common requirement to display the latest refresh time . Do we need to Add/Subtract the time from UTC time to display MY local timestamp in Power BI service? Is there any better approach to achieve this?
Hey Nomad, You can use the following code: Source = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-3,0), where "-3" is the difference between your timezone and UTC timezone.
Very useful. Thanks. The only gotcha I can think of is that this is the date/time of the data refresh from the source. If the source is pulled from another source (which mine is) then that refresh date won't be reflected. My example is that my data is in a google sheet. Unfortunately I don't know of a way to read google sheets from power bi so I need to download to an excel sheet and then pull that into pbi.
I generally add a query to the model that gets the date from the database and then display on report with the Timezone of the Server noted so folks know. Interesting thoughts on how to convert this to local time for the viewer that I will now have to consider.
Great Video as always !! I am able to apply the query to my report but when I upload online The power bi app shows 03/14/2022 5.53.ss pm(future time) and only when its updated to app whereas on Desktop and in the online workspace shows correct time.
For Auditing, Is there a way to obtain the Reports (PBIX or RDL) Last Modified Date ? Or Last Published Date to the service ? Not in the API, and Purview Hub Insights used to have it in the Inventory section, but no longer there... Also don't want to turn on Auditing logs Details in Production, for persormance reasons Thanks!
do you mean if you are live connected to an AS Tabular model? It would have to be something similar done within the model like what i showed for Power BI Desktop. Need to be a field you can use that has the info.
@@GuyInACube Thank you for your reply, I have a multi dimension AS model, I have researched about displaying the Last Process date for spicific cube but I didn't find any solution, I have added a field in a Fact Dimension with update query every time before process spicific cube to handle my scenario.
That's really great.. but when I publish this into Power bi service, it shows UTC time rather any specific time zone.. how to resolve this, please help.. Thanks..
Q: My semantic model can take up to 10 minutes to refresh all the tables, and the Refresh Data table finishes immediately. So the time stamp I see is when I hit refresh, and not when the whole model actually finished. Any suggestions on how to make the Refresh Data table update and the end?
2 ปีที่แล้ว
Thank you for the excellent content. I have a doubt. How could I get a refresh date from a specific data table?
Yep, doing this as a common practice in my reports for the same reason as you describe. To show what the last refresh time was to the end user, but also to make it easier to detect refresh failures.
I prefer to have the date shown when my data was last modified. Having a refresh date doesn't tell me much if the data driving it hasn't changed. So, in the query editor, we bring in the folder where the data source file is stored, filter for the correct file, then use the date modified.
Oo0
This was a great video and helped me a lot. I know nothing about Power Bi but need to teach myself a few things for work tasks. Tried to use Copilot/AI to learn how to do a refresh but couldn’t get it to do what I wanted. Your video solved it quickly and I feel like I understand Power BI just a tiny bit better
Is it possible to get the next schedule refresh date for a report based on the schedule refresh times defined in the powerbi service?
Hey Guy in a Cube. You have some great videos! I am just getting my feet wet in PowerBI and these are super useful. On this particular topic, this works great only when you have a published page, the published page uses a completely different timezone than my desktop apps does. Is there a way to FORCE a timezone in that date capture so both the desktop app and the published page keep the same timezone?
Thanks so much. I would say they only thing to improve on would be to change the category label to larger text size, rename table to "refreshed date" and change field from last to first such that it reads " Last Refreshed Date"
Humm, I tried this but as soon as I put my dashboard on the Power Bi Server, it used the date the server was using, which is in a different country. How would you approach this situation?
Have you got the solution for this?
I went with UTC time and then add or substract your time zone
Thank you from Melbourne Au. Only been using PowerBI about a year and believe I need to show my audience when the data was refreshed. Very easy to follow, thanks again. Mikey Mike
Very good to have this KPI to show last refresh time especially if the users access the Reports in PowerBI services web page
You guys are amazing. Just learning Power BI and you have made life so much better
Love this! Truly a best practice.
Agreed! So many people don't have something like this though. :(
@@GuyInACube Say it ain't So!!!
If i publish this to the service, then i get the Datetime of the refresh in UTC time. How can i get the last datetime refresh of my time zone ?
Presuming that you are looking for IST. Adding 5 n half hours to date.localnow() should do the job if you are not worried much about the time displayed in PBI desktop.
But I believe a better approach should exist
Our company has locations all over the US so I just report everything in EST and instead of using Adam's approach, I just create a datasource that queries the GETDATE() of our SQL server and return that in our model . Not sure of your datasource but that might help you or others.
Hey Shiva, you can use the following code:
Source = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-3,0), where "-3" is the difference between your timezone and UTC timezone.
@@joaoluismartins Thanks , but if my time zone follows Day Light Saving then this solution might not work.
@@Adi_7004 Thanks , but if my time zone follows Day Light Saving then this solution might not work.
Followed these instruction exactly and it worked great. What about correcting for time zone? How do I modify the invoked function so my app agrees with the refresh schedule I set in the app.
This is great, except when you have people from across various different time zones looking at a report. The "Local Now" of the server (in our case) is UTC. How do I get that value to translate to the logged in user's time. I'm guessing it's not possible given that when I put a simple measure of SimpleTime=NOW() and I display that as a card, it's coming up as the server's local time. Whereas when I do this on my desktop, the NOW() shows as the local time and UTCNOW() shows the UTC (I know, under redundant see redundant).
I have users in all 4 US timezones so I really can't do an offset at this point.
Standing by for an answer on this one. Same issue here.
@@JeremyCooperIsAwesomeSauce Ever find an answer to this?
@@dougnichy7018 same issue here, anyone resolved this ?
Thank you, just used this in my report. Very useful.
Needed a quick answer and you delivered! Exactly, what i needed.
Great video, so simple yet so powerful! Thanks for sharing!
So good when you can find the exact answer to your precise question, thanks!
This saved my life thank you!
My approach (working with a database of sales data that refreshes overnight) is to instead report the last invoice date from my report. But I will definitely keep this in mind for other applications.
Yup, that is another great way to do it. Depends on what you want to see. When the model was actually last refreshed, regardless of the data inside, or the last current record you have within the data. Or, maybe both :)
Simple and direct, this is why you're the best. Thank you
If I start the refresh at 10:00:00 and the refresh ends at 11:00:00, the table will have a close value to 10, right? Something like 10:00:02 or so. How could I achieve to get the end time, or something close to it?
My organization just asked me this exact question. If the report runs long...what is the ACTUAL time that shows?
Depending on how exact users want to see the last refresh date/time, maybe it's worth finding the average time of a refresh and then add that time average to the current date/time M language function. Just a thought and may not work but it's an option.
Maybe you can make the time query depend on your slow query.
Thanks, you've saved me a heap of research to get this working.
Thank you so much. Your videos are extremely helpful and straight to the point.
Hi @guyinacube, I use a calculated col in my report just create a col and enter UTCNow() function to get the date time in utc, now to show the time zone i just concatenate this function with " UTC" string and now they know when their data was last refreshed.
That is another way to do it. I usually try and push things as far back as I can. But, assuming it is by itself, that may not have a lot of overhead from a calculated column perspective.
@@GuyInACube The issue with that approach happens in the service when an end user hits the refresh button and the DAX calculates but the data isn't actually refreshed. Power Query is the only way outside the metadata in the new app experience
This was awesome. Thank you so much for this video.
One thing that would be cool is to see how to create a timestamp in your report that shows the last time a Dataflow/set has refreshed, not necessarily the report itself
Love it. I have learnt everything I know from you guys. Thanks a bunch.
Hey, thanks for the tip. One problem of publishing this to Power BI Online is that Power BI Online only works on coordinated universal time (UTC). It will change your local time zone whatever you do. So for Europeans with summer/winter time zone it is a pain to work with.
th-cam.com/video/xOj7KNqe_cI/w-d-xo.html
Any trick to overcome this ?
Excellent, so much better than the online solution that always crashes!
yeah i think this is the better solution to find the refresh issue. Appreciated
Loved the video!!!!! Thanks a lot
Hi Adam, It only shows UTC date time in power bi service.
Pls suggest how to change time zone for IST and other time zones
the only way I found it extract from the time website.
@@ducminh7568 can you please share how do you do that? I'm still stuck with UTC date time and I don't like it. thanks!
@@danielsiffredi8403 nah they way i did is extract data via website option in Power Bị and find any time website on the internet
This is great, short and to the point, Thanks.
Congrats, man, exactly I was needing!
So simple - so helpful. Great!
Nice tips, easy to apply. Thx
The KISS method: Keep It Simple and Straight. Thanx!
great and easy ! thanks
BAM! Thanks for watching! 👊
Hey Guys,
For those who are struggling with the UTC time of the Refresh inside the Service, a good approach is to use the code below:
Source = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-3,0), where "-3" is the difference between your timezone and UTC timezone.
Thank you. Made things a lot easier on me. :)
This step should be included in the video. This was key!
I don't think this accounts for daylightsaving (in the US)
If you do this on the local machine, updated and upload I assume it will work. but will it still work once you publish and updates run on the service and not from you local machine?
It doesn't work on Power BI service
That was a great tips, thanks Adam
Thank You Sir
This is amazing! You just saved me. Thank you very much.
What about displaying the NEXT scheduled refresh date. This information is available in the Power BI Service when you have Scheduled Refresh times selected. I'm just wondering if you can display something similar in Power BI Desktop.
very useful, thanks a lot for this video
so helpful, thank you so much.
thank you!
Thank you for this!
Nice video. Keep up the great work guys
Thanks for the video. It is pretty much a common requirement to display the latest refresh time . Do we need to Add/Subtract the time from UTC time to display MY local timestamp in Power BI service?
Is there any better approach to achieve this?
Try DateTimeZone.FixedLocalNow()
You guys rock!
Simple and very useful, thank you
Nice one. Thank you
Very nice video thanks !!!
Nice! Thank you.
Hi, how do you set the timezone? It's UTC on PowerBI service
Hey Nomad,
You can use the following code:
Source = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-3,0), where "-3" is the difference between your timezone and UTC timezone.
Try DateTimeZone.FixedLocalNow()
Adam, simple and useful thank you so much
This was a Very useful feature.
Very useful, thanks :)
Easy-peasy, lemon-squeezy 🤓 simple, bit very useful!
Agreed! 👊
Very useful. Thanks.
The only gotcha I can think of is that this is the date/time of the data refresh from the source. If the source is pulled from another source (which mine is) then that refresh date won't be reflected. My example is that my data is in a google sheet. Unfortunately I don't know of a way to read google sheets from power bi so I need to download to an excel sheet and then pull that into pbi.
I generally add a query to the model that gets the date from the database and then display on report with the Timezone of the Server noted so folks know. Interesting thoughts on how to convert this to local time for the viewer that I will now have to consider.
You guys are the best !!!
Appreciate that! 👊
Thanks, just fixed a clients request on a Saturday lol :D
This was very helpful. Thank you for posting this :)
This was very helpful
Great to learn this trick
What do I think? I think you guys are awesome!!!!
*Smashing the Like button*
Thanks for this!
YES! Thank you 🙏👊
Hi, will this break a direct query type report? As in make it no longer direct querying?
Cool stuff, i was looking vba coding old school method. Gr8 Video Make life easy
Thanks
I love this video! Many thanks
I think it's better to use Max last updated column (if any) from source rather than using the dataset refresh time.
is there any workaround for refreshed time in DirectQuery storage mode
Thanks again !!
Awesome, perfect and thanks for the video.
hi, is there a way to do this in DirectQuery connection since we don't manually refresh the data there?
Is it work when I refresh from the power bi service after publish ?
Share your thought.
Great Video as always !! I am able to apply the query to my report but when I upload online The power bi app shows 03/14/2022 5.53.ss pm(future time) and only when its updated to app whereas on Desktop and in the online workspace shows correct time.
Awesome, how do I get the refresh date for each table ?
Really simple and useful video thanks. Quick question: how do I remove the seconds??? I've searched for ages and can't find an answer!
Thank you 😊
That is an amazing video!
For Auditing, Is there a way to obtain the Reports (PBIX or RDL) Last Modified Date ?
Or Last Published Date to the service ?
Not in the API, and Purview Hub Insights used to have it in the Inventory section, but no longer there...
Also don't want to turn on Auditing logs Details in Production, for persormance reasons
Thanks!
So amazing!!
There are any way to get last process date for Cube and display it in PowerBI?
do you mean if you are live connected to an AS Tabular model? It would have to be something similar done within the model like what i showed for Power BI Desktop. Need to be a field you can use that has the info.
@@GuyInACube Thank you for your reply,
I have a multi dimension AS model, I have researched about displaying the Last Process date for spicific cube but I didn't find any solution,
I have added a field in a Fact Dimension with update query every time before process spicific cube to handle my scenario.
What if i wanted the different time zone date time refresh?
Can we have a query that only updates the date if something on model has changed (table settings or database update)??
Really helpful... Thank you (Y)
how about if you need to change the format eq dd/mm/yyyy to mm/dd/yyyy ...??? for DateRefresh
Does this work with schedule refresh?
That's really great.. but when I publish this into Power bi service, it shows UTC time rather any specific time zone.. how to resolve this, please help.. Thanks..
Thank you so much for all your videos.. Simple and very usefull ! You're the man ! :-)
Can we have this with power query in excel
Q: My semantic model can take up to 10 minutes to refresh all the tables, and the Refresh Data table finishes immediately. So the time stamp I see is when I hit refresh, and not when the whole model actually finished. Any suggestions on how to make the Refresh Data table update and the end?
Thank you for the excellent content. I have a doubt. How could I get a refresh date from a specific data table?
Hi Guys, I am using live connection to tabular model in my report. Is there a way to capture last cache refresh datetime to show on report?
Now a tricky one: can you suggest how to achieve same result for DirectLake based semantic model?
Great. thanks !