topic Re: Creating Production Orders based on Stocking Levels in DAX Commands and Tips

Creating Production Orders based on Stocking Levels

TorbenBoehrnsen — Mon, 28 Nov 2022 15:27:59 GMT

Hi All,
I am currently trying to build out a stock level model. See Table above:

Legend:
Week of - Date of the Monday in a week (Mon-Sun)
New Sales - Incoming Customer Orders for current Week
Total Sales - Cumulative Customer Sales up to and including currentWeek

Desired Stock - Stock Level that I would like to acchieve but not go (too much) past
Initial Stock - Stock Level at Day 0
Cumulative Orders Places - Cumulative Production Orders placed up to and including currrent Week
(We want to only Produce multiples of 10)
Cumulative Orders Places T-1 - Cumulative Production Orders placed up to not including current Week
Cumulative Orders Places T-ALT - Cumulative Production Orders placed up to ALT weeks ago
(ALT is a variable that describes our current lead time in weeks)

Cumulative Orders Places T-Alt1 - Cumulative Production Orders placed up to ALT+1 weeks ago
Orders Places - Production Orders Placed in Current Week = [Cumulative Orders Places] - [Cumulative Orders Places T-1]

Orders Received - Orders received from Production in current Week = [Cumulative Orders Places T-ALT] - [Cumulative Orders Places T-ALT1]
Cumulative Change in Stock = [Cumulative Orders Places T-ALT] - [Total Demand]
Stock Level = [Initial Stock] + [Cumulative Change in Stock]

My Big question:
How can I have the [Cumulative Orders Placed] Measure check the [Stock Level] one period Prior to ensure that is stock levels are above [Desired Stock] no new orders are placed, without getting a circular dependency?

Thank you already for all your help and ideas!
Torben

Re: Creating Production Orders based on Stocking Levels

Anonymous — Tue, 29 Nov 2022 07:32:19 GMT

Hi TorbenBoehrnsen

You can use calculate() function : calculate(expression,filter)

You can refer to the following link:

https://www.sqlbi.com/articles/understanding-circular-dependencies/.

If this does not meet your requirement, can you offer some example data and the expected output

^{Best Regards,}

^{Yolo Zhu}

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

Re: Creating Production Orders based on Stocking Levels

TorbenBoehrnsen — Mon, 05 Dec 2022 13:55:29 GMT

Sadly, the artical does not 100% answer my question, as I understand where my circular dependency is, but am not sure how to get rid of it.
At the line below you can find a example of the report that I am currently working on.
https://www.dropbox.com/s/bnj8iqsues8zxkv/Stocking%20Levels%20Sample%20Data.pbix?dl=0
My big issue is that I do not want to create a new order, if the Stock level is at or above the desired Stock.

Thank you for all the help!

Torben