Display time since last refresh

[ShellyPC]

Hi

I have a table in my dataset that contains a calculated field that is last refresh date and time.  This will be calculated every time the data is refreshed.  I display this on my report.  Now I have 2 virtually identical reports. One is set to refresh daily and the other is set to refresh quarterly.  We need the quarterly data to remain static for the whole of the next quarter.  (We have a use case where we want to see what the data looked like at the end of the last quarter).  However, I am worried that the users will confuse the two reports.  My users tend to ignore the last refresh date and simply accept the data they see as up to date.  I wanted to dynamiclly colour the display of the last refresh date and time, so that if the data is more than 24 hours old, it is big and red (or similar).

In fact, I would now like to add this to all my reports, so that no matter why the report contains old data, the users will be alerted that this data is old.

My first thought was to simply create a measure giving the number of hours (or days) between now() and last refresh date.  This works once, because nothing will cause that Dax measure to recalcuate after the first time.  It will only update when the data is refreshed.  

Is there any way I can force that measure to recalculate when a user opens the report?  Or any other way to calculate dynamically how long it has been since the last refresh?  Could I somehow fetch and use this data from the service?  

Any suggestions appreciated.

Thanks

[danextian]

Hi @ShellyPC ,

Unlike VBA, Power BI doesn't have event triggers that updates a particular measure, say, when you open it.  The only scenarios AFAIK when a measure updates it are when:

• a user crossfilters (via visual elements) or fitlers (via slicers) but the moment you go back to the previous state the measure goes back to the previous state as well so that will only add confusion to the users
• a user hits the refresh visuals icon on the upper right of the report in Power BI Service.

[ShellyPC]

Thanks for the input.  I think you are probably correct.  Before I mark your answer as the solution there is one more piece I want to investigate.

Using the Deneb visual with Vega, it is possible to put a ticking clock on the report.  I do not know however if it is possible to take the time from the clock and somehow use that to put up a message or change a color.  

I have never looked at Deneb before and until today, had never heard of Vega, so this will take some time.  I will come back here and post my findings.

[danextian]

Hi @ShellyPC 

Deneb is not my forte and since this is related to customizations using it, I would suggest you posted a separate topic so those who are more familiar with it could give you their suggestions.