How to calculate inventory with past sales and purchases?

I have three tables in PowerBI, a sales table with articles sold over a year, the purchases of the articles within a year and the inventory at a certain timestamp.

I would now like to be able to calculate the inventory for every day.

The data could be exemplified like this:

I tried creating a column for every product and then 365 rows to show the inventory for every day. However, I do not know how to incorporate the inventory of 29.03.2019.

Also, for demonstration I only used three articles. If, however, I have a dataset of 10.000 products, what would be the ideal way to calculate this to be able to display it properly in a dashboard lateron?

Solution

First I created a StockTable where we have a purchase & sales combined:

StockTable = 
UNION(
    ADDCOLUMNS(Purch;"Event";"Purchage") ;
    SELECTCOLUMNS(Sold;"Article";Sold[Article];"PurchageDate";Sold[SalesDate];"Amount";-Sold[Amount];"Event";"Sold")
)

As we need to ensure the relational model is correct, I added a new relation from Invent to StockTable 1:*

Next I added a column Stock on the StockTable, this calculates in relation to the inventory date of the article the stock on the specific date. It only has the records when a real change did happen (the 365 days is not needed).

Stock = 
var lastStockDate = RELATED(Invent[Date])
return if (StockTable[PurchageDate] < lastStockDate;
    RELATED(Invent[Amount in inventory]) + CALCULATE(SUM(StockTable[Amount]); FILTER(StockTable;StockTable[Article] = EARLIER(StockTable[Article]) && StockTable[PurchageDate] >= EARLIER(StockTable[PurchageDate]) && StockTable[PurchageDate] <= lastStockDate)) ;
    CALCULATE(SUM(StockTable[Amount]); FILTER(StockTable;StockTable[Article] = EARLIER(StockTable[Article]) && StockTable[PurchageDate] <= EARLIER(StockTable[PurchageDate]) && StockTable[PurchageDate] > lastStockDate)) + RELATED(Invent[Amount in inventory]))

Now it is possible to create a graph which shows the stock per article: