Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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?
@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?
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
HI, no still there, no solution except for excell formulas but one is terribly slow
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.:
Hi
updated my original post
updated, let me know if more clarifications needed
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |