You want to calculate the stock level for each week for the next 13 weeks based on the current stock, planned receipts, and planned deliveries. The tricky part is involving the "week minus 1" in the calculation.
First, you'll need a calendar table that has all the weeks you're interested in. If you don't have one, you can create one in Power BI or use a DAX function like CALENDAR or CALENDARAUTO.
Now, let's create a measure for the stock level in week X.
Start by calculating the current stock level for each article:
CurrentStock = SUM(Database_Stock[InStock])
Next, calculate the planned receipts up to week X minus 1. This means you'll sum all the receipts up to the week before the current week in your calendar table:
PlannedReceipts =
CALCULATE(
SUM(Database_GoodsReceipt_Lines[QuantitityReceipts]),
FILTER(
Database_GoodsReceipt_Lines,
Database_GoodsReceipt_Lines[Modified] < MAX(Calendar[Date]) - 7
)
)
Here, I'm assuming your calendar table is named "Calendar" and has a column named "Date". The MAX(Calendar[Date]) - 7 part ensures you're looking at the week before the current week.
Then, calculate the planned deliveries up to week X:
PlannedDeliveries =
CALCULATE(
SUM(Database_GoodsDelivery_lines[QuantitityDelivered]),
FILTER(
Database_GoodsDelivery_lines,
Database_GoodsDelivery_lines[Modified] <= MAX(Calendar[Date])
)
)
Finally, combine these measures to get the stock level for week X:
StockLevelWeekX = CurrentStock + PlannedReceipts - PlannedDeliveries
Now, when you drag this measure into a table or matrix visual with weeks on the rows, you'll see the stock level for each week. Remember to filter your visual to the next 13 weeks to get the desired output.
