Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dimotori
Frequent Visitor

Recursive calculation problem for supply modeling problem

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#MINMAXLead timeINV_0OutboundReorderPipelineInboundINV_1Total INV
11,2352,4693002,4690002,469
21,2352,4693002,469002,469
31,2352,46930002,46902,469
41,2352,4693374002,4692,0952,095
51,2352,46932,09537400

0

1,7211,721
61,2352,46931,7213740001,3471,347
71,2352,46931,3473741,4960

0

9732,469
81,2352,469397329901,49606742,170
91,2352,469367429901,49603751,871
101,2352,4693375 299

0

0  1,4961,5721,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. 

 

 

9 REPLIES 9
Stachu
Community Champion
Community Champion

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?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂


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

Any takes, anyone?

Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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

Stachu
Community Champion
Community Champion

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


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi

 

updated my original post

Anonymous
Not applicable

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

updated, let me know if more clarifications needed

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors