cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmace1 Member
Member

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

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?

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator v-yuezhe-msft
Moderator

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

@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.
1.JPG

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.
2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Highlighted
Moderator v-yuezhe-msft
Moderator

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

@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.
1.JPG

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.
2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mmace1 Member
Member

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

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

ShrikantKhanna Regular Visitor
Regular Visitor

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

Thanks Lydia, this is helpful.