Week Over Week comparison using daily files in the same folder

[klarrick]

I'm building a query that accesses a folder where daily files are saved.  I need to compare the most recent file to the file from the same day last week (the 6th file from the top of folder).  The run date is a column in the data files as well as embedded in the file name.  How do I best set up the query to access only the data from the 1st and 6th files, and then compare certain measures by line from these 2 files? 
Here's sample data:
From today's file (top row of the folder): 
Report Run Date       EventSiteId             Actual Total $
2/22/2023 0:00          1223                       41202.56
From one week ago file (6th file from the top of the folder):
Report Run Date       EventSiteId             Actual Total $
2/15/2023 0:00           1223                       30653.45
Restating the above question using this example: Based on EventSiteID, how can I compare today's actual to one week ago actual using data from these 2 files?

[ImkeF · Accepted solution]

Hi @klarrick ,
if it is always the 6th file, you could add an index column once you have imported from folder.
If it is 0-based, choose values 0 and 5 from that index column. That leaves you with the relevant files.
Expand out the file content, check column "Report Run Date" and delete it. Then check the Index-column and pivot on it with column "Actual Total $" as amount-column.
This will return 2 new columns: 0 and 5: Check them and add column that subtracts one from the other.
If you want to keep the a reference day in your table, add it as an additional column after you have created your index with the following formula: if [Index] = 0 then [Report Run Date] else null 
Then fill down on that column.