Recursive calculation problem for supply modeling problem

[Anonymous]

Hi all, very new to.  Please advise if bellow case may have a solution in DAX, Power query.  

Have a model in Execel to identify reorder points based on Min/Max levels over 52 time periods (year). works perfect with 1 item, start having difficulties with 3,000 part #s (array formulas and chain calculations) therefore trying  to rebuilf that with DAX.   Was able to reproduce all columns in DAX except for [Reorder] and [Imbound], spits out circular dependancy error. I understand that DAX was not ment to be used for recursive calculations, however, I am sure there must be a workarround. Bellow is an abstract of Excell spreadsheet (with 1 part #).  Min/MAX levels will be optimized in outside calculation/what if scenarios.  therefore trying to keep it in DAX(dynamic)  

Week#	MIN	MAX	Lead time	INV_0	Outbound	Reorder	Pipeline	Inbound	INV_1	Total INV
1	1,235	2,469	3	0	0	2,469	0	0	0	2,469
2	1,235	2,469	3	0	0	0	2,469	0	0	2,469
3	1,235	2,469	3	0	0	0	2,469	0	0	2,469
4	1,235	2,469	3	0	374	0	0	2,469	2,095	2,095
5	1,235	2,469	3	2,095	374	0	0	0	1,721	1,721
6	1,235	2,469	3	1,721	374	0	0	0	1,347	1,347
7	1,235	2,469	3	1,347	374	1,496	0	0	973	2,469
8	1,235	2,469	3	973	299	0	1,496	0	674	2,170
9	1,235	2,469	3	674	299	0	1,496	0	375	1,871
10	1,235	2,469	3	375	299	0	0	1,496	1,572	1,572

table calculated line by line in a secuence of columns 

where:

[Week#], [LT Weeks], [Min], [Max]  and [Outbound] - given all other need to be calculated

[Week#] - time period aka index

[LT weeks}- lead time (time between Reorder  and Inbound)

[Min] - reorder point -reorder happens when total inventory reaches this point

[INV_0] - Oppening Inventory = Closing inventory from previouse period

[Reorder] - reorder quantity = if([INV_1]-[DEMAND]+[INBOUND]+[PIPELINE]<=MIN, [MAX]-([INV_0]-[Outbound]+[INBOUND]+[PIPELINE]),0)

[Pipeline] - sum of quantities reordereded in preceeding lead time interval -1

[Inbound] - quantity reordered olead time periods ago = [Reorder]_[Week#]-[LT Weeks]

[INV_1] - closing inventory = [INV_0]-[Outbound]+[Inbound]

Total inv - net inventory +everything ordered earlier and on its way  = INV_1+[Pipeline].

now look at possible solution with power query as well, but would prefer that being more dynamic. and again major problem is scalability (3000+ pparts over 52 week period) 

help would be much appriciated. 

[Stachu]

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

I don't really see the need for recursion here - or do you mean referencing previous row by that?

[Anonymous]

---

@Stachu wrote:

I don't really see the need for recursion here - or do you mean referencing previous row by that?

---

Well, "current" [INV_1] refers [Inbound] that refers to [Reorder] that happened LT intervals ago, which refers to [INV_0], Inbound and [Pipeline] which happened LT intervals ago and so on.  As you start agregating DAX starts complaining on circular dependancy.  

[Anonymous]

Any takes, anyone?

[Stachu]

Sorry for late reply - do you still have this issue, or did you manage to solve it yourself? I may have a closer look at it today if you still need it

[Anonymous]

HI, no still there, no solution except for excell formulas but one is terribly slow

[Stachu]

I don't see the [LT Weeks] column that you mentioned, or is it [Lead Time]? Can you share just the input table?

Do I read it correctly that [#Week] and [LT Weeks] are respectively week index, and number of weeks, and all other columns are quantities?

Also, looking at your calculation logic, few columns are missing/not explained (some can be calculated with the table you posted, but not using the 5 columns you specified to be given), e.g.:

• Oppening Inventory
• Closing Inventory
• reorder quantity

[Anonymous]

Hi

updated my original post

[Anonymous]

No idea how such calculations should be dynamic... Secondly, the description of the problem is not the clearest in the world...

Would you mind describing this in a more understandable way? Still I think this is a job for Power Query, not for DAX.

Best
D

[Anonymous]

updated, let me know if more clarifications needed