Need Help with a DAX measure I need to create. Urgent

[slhyrkl]

I want you to create a DAX measure in Power BI that calculates the excess inventory we will have according to the weekly demand based on some Parameters Like safety stock, max min amount of inventory levels, weekly demand, beginning On-hand quantity, MOQ and FLM, etc. I defined my parameters as shown below;

• Max Inventory Quantity (MIQ): The maximum quantity you can store.
• Safety Stock Quantity (SSQ): The minimum quantity you need to maintain to prevent stockouts.
• Demand within Lead Time (DLT): The quantity demanded within the supplier lead time.
• On-Hand Quantity (OHQ): The current quantity available in your inventory.
• Minimum Order Quantity (MOQ): The smallest quantity that can be ordered from the supplier.
• Weekly Demand (WD): The expected demand for each item for the next year, broken down by week.

I created a measure that takes only FLM and MOQ into consideration to calculate order quantity as shown below;

Order qty =
VAR MinOrderQty = MAX(MOQ[MIN_])
VAR FixedLotMultiplier = MAX(MOQ[FLM_MOQ])
VAR WeeklyDemand = SUM('MRP Future Demand'[Rounded QTY])
VAR MOQFLMMismatch = IF(FixedLotMultiplier <> MinOrderQty, "no", "yes")

-- Initial Order Quantity Calculation
VAR OrderQty1 =
IF(
WeeklyDemand <= MinOrderQty,
MinOrderQty,
MinOrderQty * CEILING(DIVIDE(WeeklyDemand, MinOrderQty), 1)
)

-- Additional Order Quantity Calculation if there is a mismatch
VAR AdditionalOrderQty =
IF(
WeeklyDemand > MinOrderQty,
IF(
MOQFLMMismatch = "no",
FixedLotMultiplier * CEILING(DIVIDE(WeeklyDemand - OrderQty1, FixedLotMultiplier), 1),
0
),
0
)

-- Total Order Quantity Calculation
VAR OrderQty =
IF(
WeeklyDemand <= MinOrderQty,
MinOrderQty,
OrderQty1 + AdditionalOrderQty
)

-- Return Total Order Quantity
RETURN OrderQty

You can see the tables I pulled the MOQ and FLM numbers in that dax formula. Also use the table and data named MRP Future Demand[Rounded QTY] (table-column) for WD as I did in my measure. Your MIQ is Expected Inventory calc[UPPER_CONTROL_LIMIT] (Table-column), SSQ is Expected Inventory calc[SAFETY_STOCK] (Table-column), DLT is Expected Inventory calc[LEAD_TIME_DEMAND] (Table-column), OHQ is Expected Inventory calc[BEGINNING_ON_HAND] (Table-column), please see MOQ and FLM in the DAX Measure I shared. I will be adding the screenshot of each table I need to use as I mentioned.

I want to start with my OHQ amount (Expected Inventory calc[BEGINNING_ON_HAND]) for the current week and reduce the OHQ every week by the weekly demand amount (MRP Future Demand[Rounded QTY]) for each week. If the final value is equal to or less than SSQ+DLT for a week, then place an order based on  MOQ-FLM DAX for measure for finding the order quantity and create a dynamic measure like this. 

For example As you can see in the screenshot below, On Hand Quantity is 117 as shown in the first table on the right as I highlighted. So I should start with 117 as starting point for the current week and since Demand for week 26 is 10 as shown in the Supply-Demand Trend by Weeks Table, My excess inventory for 26 2024 in the Excess Inventory by Future Demand table should be shown as 107 (117-10). That should be your starting point for the next week which is 27 2024. Since my demand is 11 for that week, Excess inventory should be shown as 96 (107-11). Use the same logic till my excess inventory for a week is equal or less than SSQ+DLT and place an order by using the MOQ FLM measure I shared with you and show excess inventory as ordered qty + new calculated Excess inventory for the week whatever the lead time is for the specific part. My lead time (week) is stored in Main Table as LT column. Since in my example in screenshot, lead time is 6 weeks for part 101322, you should show ordered qty+ new calculated excess inventory qty the 6 weeks after I placed an order as new excess inventory and use the same logic by reducing that number by demand for the next weeks until it is equal to or less than SSQ+DLT;

I already sorted the demand for each week of the next year with this column, MRP Future Demand[WeekYear] and MRP Future Demand[Rounded QTY]. When I put WeekYear column int he columns and Rounded QTY in the values sections on a table, it shows me the demand for each week for the next 1 year starting with the current week. the WeekYear column only shows me the week of the year and I created that to sort the data properly in a table visual by using this measure by using the DATE_ column in the table as shown below; FYI just incase it may create a confussion

I would appreciate it if you can help me create this measure to use in my last table to show the expected excess inventory for the future.

[Anonymous]

Hi @slhyrkl ,

Thank you for providing such a comprehensive description of your requirements. I appreciate the level of detail you’ve shared. To ensure I address your case accurately, could you please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards