Forum Discussion
Help Needed: Replicating Recursive Excel Depletion Logic in DAX (Running Total with Conditional Sub)
Hi Sasi_2108 ,
Thanks for clearing that up, that’s exactly how the solution works. It doesn’t just match totals at the Key level; it actually splits the AddPriorityQty across each line item in order, based on the running Pre-alloc quantity. For each Key, the model goes row by row, checks how much of the priority quantity has already been used, and then gives each row either its Pre-alloc amount or whatever’s left. This way, the allocation happens line by line and stops when the pool runs out, just like the Excel logic the business is used to.
Super UserHi Sasi_2108,
DAX is not recursive and will never be. M is.
Anyway, before giving up on DAX, let's see what we can invent
But this way to explain the formula is diffucult here, can you comment the forumlas to help us grabbing the key part of the recursion?
| Running Qty to Add | Flag and Qty to add Priority | Flag Partial | Partial Qty | Flag Filter | Total Add Priority |
| =IF(D2=0,0,IF(C2<>C1,IF(D2<A2,D2,MIN(D2,D2-A2)),IF((D1-A2)<0,D1,D1-A2))) | =IF(D2=0,0,IF(C2<>C1,IF(D2=L2,0,A2),IF(L1<A2,0,A2))) | =IF(AND(E2>0,F2=0),"Partial","") | =IF(G2="Partial",IF(SUMIF($C$1:C1,C2,$H$1:H1)>=D2-SUMIF(C:C,C2,F:F),0,D2-SUMIF(C:C,C2,F:F)),0) | =IF(OR(F2>0,H2>0),"YES","NO") | =IF(I2="YES",F2+H2,0) |
Thanks
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Thank you so much for your swift response.
If ADD Priority Qty =0, then running qty is 0,
IF(key are not same (X1=X2) (If ADD Priority Qty < Pre-alloc, ADD Priority Qty, min(ADD Priority Qty, ADD Priority Qty -Pre-alloc)), IF(previous value of Running qty - Pre-alloc)<0, previous value of Running qty, previous value of Running qty- Pre-alloc)
For the first row, key is not same, 46<70, so 46 is the output,
For the second row, Key is same , 46-8<0, 46-8=38
For the third row, key is same, 38-9<0, 38-9=29
For the fourth row, key is same, 29-2<0, 29-2=27
For the fifth row, key is same, 27-1<0, 27-1=26
For the sixth row, key is same, 26-2<0, 26-2=24
For the seventh row, key is same, 24-4<0, 24-4=20
For the eighth row, key is same, 20-1<0, 20-1=19
For the ninth row, key is same, 19-3<0, 19-3=16
For the tenth row, key is same, 16-19-<0, 16,
For the eleventh row, key is same, 16-1-<0, 16-1=15
If you notice the Flag and Qty to add Priority the sum will not cross the previous row sum value
Like 46+0 38+8, 29+8, 27+2 etc. if running qty is fixed, then remaining logic can be fixed easily. The challenge is on the running qty.