Stock planning with DAX

Anonymous — Thu, 14 May 2020 10:45:40 GMT

We need insight in future stock levels on a weekly basic. Basic calculation is as follow:

Current stock level per article

plus

Planned receipts (purchase) up to week X minus 1

minus

Planned deliveries (sales) up to week X

= stock level in week X

We want this calculation for all articles for all current and future 13 weeks.

Database Stock

ItemId (article)

InStock

Modified (date)

Database GoodsReceipt_Lines (purchase)

ItemId (article)

QuantitityReceipts

Modified (date)

Database GoodsDelivery_lines (sales)

ItemId (article)

QuantitityDelivered

Modified (date)

The challenge for me is how to involve the week minus 1 in DAX.

Hopefuly somebody can push me in the right direction.

Re: Stock planning with DAX

technolog — Sun, 17 Sep 2023 17:54:36 GMT

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.

topic Re: Stock planning with DAX in DAX Commands and Tips

Stock planning with DAX

Re: Stock planning with DAX