Taking a snapshot of a table, keeping it 7 days, then seeing how many of the entries are still in it

[mmace1]

I'd be pulling in this simple table with only 2 columns.

Name, Date

I'd like Power BI to store this table, then when it's 7 days from the date of creation, compare it to the present version of the table, and see how many of of the files are still present. 

And then moving forward, everyday it'd compare now vs. the version created 7 days prior.

(If it helps, what I'm actually doing is looking in a folder to see how many of the files it, are still there after 7 days.  So the Name is the file name, and the Date is the file's createdate). 

Is this possible in an automated sense?

[v-yuezhe-msft · Accepted solution]

@mmace1,

I am afraid that it is not possible to automate above process in Power BI Desktop. In your scenario, import the table twice in Power BI Desktop, for the first imported query, disable “Include in report refresh” option in Query Editor.


Then create relationship between  the two imported tables and go to Report view, create the following similar columns in the first table.

Column = RELATED('Sheet5 (2)'[Create date])
Checkday = IF(DATEDIFF(Sheet5[Create date],Sheet5[Column],DAY)=7,1,0)

This way, create a table visual using fields of the first table, and set the value of Checkday column to 1 in Visual level filters.  After that, click “Refresh” button in Power BI Desktop each time,  and you can see that the data is refreshed in the second table and you can view the existing files in the table visual.


Regards,
Lydia

[ShrikantKhanna]

Thanks Lydia, this is helpful.

[mmace1]

OK, thank you!  It's good to have a definitive answer as to what's possible.