Forum Discussion
Impactful Individualdetecting when last refresh was from within pbix
hi, im justthe messenger here.
we inheirited a report that defaults to yesterday in the "as of date" display. Unfortunately, if the refresh hasnt run yet, that isnt true. is there a way from within pbix to detect when the last refresh ran? if it didt run yet this am, it actually should default to 2 days ago. because of post dating in transactions, it would be somewhat risky to make this decision based on the data content.
Hi db042190 ,
Yes if you are looking at the expression today it will return the current date, however since you are making a refresh of your semantic model (assuming import mode) this will be picking up the date of that refresh so if you do it now it will get July 8th however if you do not refresh your semantic model that value would be "frozen".
7 Replies
- tayloramy
Super User
Hi db042190,
Is this model using Import mode, or DirectLake/DirectQuery mode?
If it is using import mode, then MFelix is correct, adding a calculated table that returns the current date and time is all that you need. This calculated table will be calculated on model refresh.If you're using DirectLake or DirectQuery, then it's a little more complicated.
You would need to have a "loaded date" or similar in your data to determine when the data was refreshed, as with both these storage modes data is not stored in the model, so the data displayed is seperate from when the model refreshes.
In my processes, I always include an ingest timestamp when I pull data into OneLake, and then I can take a max of that ingest timestamp to display the last refresh date for my users.
db042190thx amy.
- db042190
i gave this some thought yesterday. In our case i think both mfelix and amy are right and we need to do a little of both. We have only pro and without researching how much that impacts the following, here's the story. On sundays we run our etl much later than the rest of the week. due to pbi limitations in refresh flexibility, we finally got around to a second refresh schedule to accomodate the late sunday etl start/finish. Unfortuanately, even with mfelix's more sophisticated "extra table" in the SM, pbi cant determine the as of date purely by what the extra table says. The last etl run date would always have to be an additional factor in that decision. in some ways, this argument can be extended to weekdays if/when our etl is delayed. and there is one use case there that i wont get into here. but unless im not thinking this thru, our pbi needs to consider both dates/times, refresh and last etl run to make the right decision. which also means the upper limit of our planned date range filter needs to be a little smarter than orignally thought.
- MFelix
Hi db042190 ,
For this create a new Table (Power Query or DAX) that get's the current date this table since the table get's new date value only when the model is refresh this will get the date of the last refresh.
Just replace the "as of date" by the value on that table.
For DAX just use the TODAY() formula and for Power Query use the DateTime.Date(DateTime.LocalNow())
- db042190
thx mfelix. i think i understand. Today() on its own really is Today(). and is where our shortcoming starts. i think what u are saying is that refresh time, referencing it as the lone value in a pbix table would do the trick because rendering is always based on what the SM says and that vale would already be in the SM for imports.
am i understanding ?
- MFelix
Hi db042190 ,
Yes if you are looking at the expression today it will return the current date, however since you are making a refresh of your semantic model (assuming import mode) this will be picking up the date of that refresh so if you do it now it will get July 8th however if you do not refresh your semantic model that value would be "frozen".
