Hi, I would like to know what is the best practice in achieving the following.
I have an inventory report. Sources for this report are:
- SQL database of actual stock of the main warehouse that is being overwritten with every input, so there is no historical data available,
- and several reports from customers with data about their stock of products sold to them. These reports come (with a few exceptions) weekly with a file name "name of the customer" & week-year" (for example Amazon_30-2022). I can extract the week number from the file name and combine all the different data sources into one table.
If it wasn't for the SQL database as a data source, analyzing historical data (previous weeks) would be quite simple as I have a calendar table formated to weekly table and I can use it in a slicer. Problem is that if I want to see the inventory of lets say week 25, I see the correct numbers of the stock of the customers but the actual (week 30-2022) stock of the main warehouse (SQL database) as this data source, as I mentioned earlier, does not store history and is being overwritten daily.
The solution I've come up with so far is that the user of the report will export consolidated data using some Power Automate stuff at some point, store it somewhere as another data source and there will be another query that will load this historical data. But I bet there is a more elegant way to do it.
- also worth mentioning: there are approx. 10 reports from different customers and the file names are changing every week, so I have to use Import from folder and every file has its own query as they are obviously structured differently,
- a "timestamp" has to be made manually I think, because the SQL data are constantnly changing. So some user will have to hit some export button for example every Friday at 10 AM (if there is no other way)
Do you have any ideas or some best practice tips how to solve this?
Thx
