Creating Production Orders based on Stocking Levels

[TorbenBoehrnsen]

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

[TorbenBoehrnsen]

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 

[Anonymous]

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.}