DAX for forecasting inventory with required replenishment quantity by week.

[dgdlux]

Hello all,
I have been working for quite a while trying to construct a single or series of measures in DAX to calculate the required inventory supply based on the scheduled demands. I have an inventory adjustment table of dates and of whole numbers that represent inventory adjustments categorized by adjustment type. From this I need a DAX function to return the quantity required of supply by week that will satisfy the safety stock requirement as well as a fixed order quantity.

Inventory Count = entered counts from warehouse

Item	Order Type	Qty	Date
0801	Inventory Count	1000	10/23/23 0:00
0801	Inventory Count	1000	10/23/23 0:00
0801	Inventory Count	3000	10/23/23 0:00
0801	Scheduled Work	-6000	11/6/23 8:05
0801	Scheduled Work	-5000	11/6/23 12:26
0801	Scheduled Work	-10000	11/6/23 15:55
0801	Planned Work	-10000	12/1/23 0:00
0801	Planned Work	-4000	12/8/23 0:00
0801	Planned Work	-6000	12/15/23 0:00
0801	Planned Work	-10000	12/22/23 0:00
0801	Scheduled Work	-2000	1/8/24 7:00
0801	Planned Work	-6000	1/12/24 0:00

Fixed Order Qty = 8,000 
Safety Stock = 4,000

The same or additional function must then add the resulting value into the forecasted inventory for that same week that it was supplied to be used to calculate the required inventory for the next week. The replenishment should be triggered when the forecasted inventory drops below 0.

Currently this operation is done completely manual in Excel, with a calculator by my side and calculating the net change in inventory quantity for each week. The result should be a perpetual supply forecast based on the demands for just in time production and so that the supply quantity will satisfy a safety stock but not trigger a replenshment until the inventory is forecasted to drop below 0. 

I can not get away from circular dependency errors when trying to calculate the required supply into the perpetual or forcasted future inventory. I feel that I need to iterate the calculation over each row of the data within the measure by utilizing a SUMX function along with the Earlier function but I am having trouble working through it. 

Any help is greatly appreciated.

[dgdlux]

Hi All - thank you for your help. 
@123abc , I think your solution is on the right track but @v-zhangti is correct in that I need to provide a better description as I did not explain very well. I have a series of images to illustrate.

My goal is to use Power BI and DAX to replace a manual process I currently do in Excel. Which is to calculate just in time delivery of materials for production. The source data is the transaction schedule of planned incoming purchase orders, inventory count inputs, scheduled work, and planned work.  

Full table:

#1 Full List

Step 1:Filter Table by item
#2 Filter List
Filtered Table:
#3 Filtered by Item. 

Start running total by week. If the sum of running total is >0 then the supply for that week = 0
 #5 Running Total by Week

The week the inventory drops below 0 a new order is to be calculated.
#6 If running total by Week < 0 then
#7 Execute formula to calculate supply order
Formula to calculate order quantity that is to be added to the perpetual running total to allow calculation the following week.
#8 Safety stock does not trigger an order (JIT). Only a negative value but the supply order includes safety stock.
If week demands do not exceed the available inventory (Running total + running total of previous week's supply > 0) then 0. No order required.

#9 Subsequent weeks forecast. Each weeks supply order should leave inventory above safety stock but below the next lot order size.

[123abc]

Thank you for providing the detailed clarification and images. Based on your description, it seems like you want to create a Power BI solution for just-in-time (JIT) inventory management, where supply orders are generated when the inventory drops below a certain threshold, taking into account safety stock and lot order size. To implement this in Power BI using DAX, you can follow these steps:

1. Data Modeling: Ensure that your data is loaded into Power BI, and you have a table that includes the necessary columns: Item, Date, Qty, and Adjustment Type.

2. Create Key Measures: Define some key measures that will be used in subsequent calculations. These measures include:

   • Running Total: Calculate the running total of inventory adjustments by week. Use a measure like this:

RunningTotal =
SUMX(
FILTER(ALL('YourTable'), 'YourTable'[Item] = SELECTEDVALUE('YourTable'[Item])),
[Qty]
)

1. • Previous Week's Running Total: Calculate the running total of the previous week. You can use EARLIER to get the running total for the previous week.

2. Generate Supply Orders: Create a measure to generate supply orders when inventory drops below the threshold. This measure should consider safety stock and lot order size. Here's a simplified example:

SupplyOrder =
IF(
[RunningTotal] < [Safety Stock],
[Lot Order Size] + [Safety Stock] - [RunningTotal],
0
)

1. Adjust this formula based on your actual business rules and data.

2. Calculate Forecasted Inventory: Create a measure to calculate forecasted inventory for subsequent weeks. Use the supply orders to update the forecasted inventory:

ForecastedInventory =
VAR CurrentWeek = SELECTEDVALUE('YourTable'[Date])
VAR LastWeek = CALCULATE(MAX('YourTable'[Date]), ALL('YourTable'), 'YourTable'[Date] < CurrentWeek)
RETURN
IF(
CurrentWeek = MIN('YourTable'[Date]),
[Initial Inventory] + [SupplyOrder],
IF(
[SupplyOrder] > 0,
[ForecastedInventory] + [SupplyOrder] - [Lot Order Size],
[ForecastedInventory]
)
)

1. Adjust [Initial Inventory] to represent your initial inventory on the first date.

2. Visualize the Data: Create visuals in Power BI that display the calculated forecasted inventory, supply orders, and other relevant information over time.

This approach should help you model your data and create DAX measures for JIT inventory management in Power BI. You may need to refine and expand these measures to suit your specific business rules and data structure.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[v-zhangti]

Hi, @dgdlux 

Based on the example data you provided, is it possible to tell what kind of output you expect? You can show it as a picture or excel.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[123abc]

It looks like you are trying to calculate the required inventory supply for each week based on scheduled demands, safety stock, and a fixed order quantity, and you want to maintain a perpetual supply forecast. This can be a bit complex due to the interdependencies, but you can achieve this with a DAX measure. Here's an approach to solve this problem.

To calculate the required supply by week and maintain the perpetual supply forecast, you can create a DAX measure. I'll break this down into steps:

1. Calculate Net Demand: First, calculate the net demand for each week by subtracting the scheduled and planned demands from the Inventory Count.

Net Demand =
SUMX(
FILTER(
'YourTable',
'YourTable'[Order Type] IN {"Scheduled Work", "Planned Work"}
),
'YourTable'[Qty]
) + [Fixed Order Qty]

Calculate Forecasted Inventory: Calculate the forecasted inventory by adding the current week's supply (previous week's ending inventory) to the current week's net demand.

Forecasted Inventory =
SUMX(
FILTER(
ALL('YourTable'),
'YourTable'[Date] <= MAX('YourTable'[Date])
),
'YourTable'[Qty]
) + [Net Demand]

Calculate Required Supply: Determine the required supply based on the safety stock and the forecasted inventory. If the forecasted inventory falls below the safety stock, order enough to bring it back to the safety stock level. Otherwise, order enough to cover the net demand.

Required Supply =
IF([Forecasted Inventory] < [Safety Stock], [Safety Stock] - [Forecasted Inventory], [Net Demand])

With these measures in place, you can create a table or chart to view the required supply by week.

Please replace 'YourTable' with the actual name of your data table. You can use these DAX measures in a Power BI report or Excel Power Pivot, and they should help you calculate the required supply while maintaining a perpetual supply forecast. The calculations will handle the interdependencies between weeks and provide you with the necessary order quantities.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.