Please advise if the below case may have a solution in DAX, Power query.
Have a model in Excel to identify reorder points based on Min/Max levels over 52 time periods (year). It works perfectly with 1 item, start having difficulties with 3,000 part #s (array formulas and chain calculations) therefore trying to rebuild that with DAX. Was able to reproduce all columns in DAX except for [Reorder] and [Inbound], spits out circular dependency error. I understand that DAX was not meant to be used for recursive calculations, however, I am sure there must be a workaround.
This is an abstract of an Excel spreadsheet (1 with part #)
where:
Safety, Min & Max - stock levels
LT Weeks - lead time (time between ordering and restocking)
INV_0 - opening inventory = Closing inventory from the previous period
INV_1 - closing inventory
Demand - outbound quantity = separately modeled forecast
Inbound - inbound = Quantity reordered [LT Weeks] periods ago
Reorder - reorder quantity =
IF (
[INV_1] - [DEMAND] + [INBOUND] + [PIPELINE] <= MIN,
[MAX] - ( [INV_0] - [DEMAND] + [INBOUND] + [PIPELINE] ),
0
)
Pipeline - sum of quantities reordered in preceding [LT weeks] interval -1
Total inv = INV_1 + [Pipeline].
Help would be much appreciated.
