Forum Discussion
Help Needed: Replicating Recursive Excel Depletion Logic in DAX (Running Total with Conditional Sub)
Hello Community,
I am trying to replicate a complex inventory allocation logic from Excel into Power BI. I have a dataset where I need to subtract "Pre-allocations" from a "Starting Quantity," but with a specific condition:
I have attached a sample dataset and the Excel formulas used to generate the expected results.
The Core Logic (Excel Formulas):
Key1 : Grouping level.
Pre-alloc : The quantity to be subtracted.
ADD Priority Qty : The total starting pool for the group (same for all rows in a Key).
Running Qty to Add : This is the recursive column. What is the best way to handle this in DAX? I cannot use power query because some of the base columns/base measures/tables are computed in power bi which I cannot repliacate the same in power query. need dax help to achieve the results for the below.
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((E1-A2)<0,E1,E1-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)
Test Data here with columns reference.
| A | B | C | D | E | F | G | H | I | J |
| Pre-alloc | Row Index | key1 | ADD Priority Qty | Running Qty to Add | Flag and Qty to add Priority | Flag Partial | Partial Qty | Flag Filter | Total Add Priority |
| 70 | 1 | X1 | 46 | 46 | 0 | Partial | 15 | YES | 15 |
| 8 | 2 | X1 | 46 | 38 | 8 | 0 | YES | 8 | |
| 9 | 3 | X1 | 46 | 29 | 9 | 0 | YES | 9 | |
| 2 | 4 | X1 | 46 | 27 | 2 | 0 | YES | 2 | |
| 1 | 5 | X1 | 46 | 26 | 1 | 0 | YES | 1 | |
| 2 | 6 | X1 | 46 | 24 | 2 | 0 | YES | 2 | |
| 4 | 7 | X1 | 46 | 20 | 4 | 0 | YES | 4 | |
| 1 | 8 | X1 | 46 | 19 | 1 | 0 | YES | 1 | |
| 3 | 9 | X1 | 46 | 16 | 3 | 0 | YES | 3 | |
| 19 | 10 | X1 | 46 | 16 | 0 | Partial | 0 | NO | 0 |
| 1 | 11 | X1 | 46 | 15 | 1 | 0 | YES | 1 | |
| 28 | 12 | X2 | 1 | 1 | 0 | Partial | 1 | YES | 1 |
| 4 | 13 | X2 | 1 | 1 | 0 | Partial | 0 | NO | 0 |
| 34 | 14 | X2 | 1 | 1 | 0 | Partial | 0 | NO | 0 |
| 29 | 15 | X2 | 1 | 1 | 0 | Partial | 0 | NO | 0 |
| 38 | 16 | X2 | 1 | 1 | 0 | Partial | 0 | NO | 0 |
| 16 | 17 | X2 | 1 | 1 | 0 | Partial | 0 | NO | 0 |
| 6 | 18 | X3 | 2 | 2 | 0 | Partial | 0 | NO | 0 |
| 6 | 19 | X3 | 2 | 2 | 0 | Partial | 0 | NO | 0 |
| 2 | 20 | X3 | 2 | 0 | 2 | 0 | YES | 2 | |
| 2 | 21 | X3 | 2 | 0 | 0 | 0 | NO | 0 | |
| 4 | 22 | X3 | 2 | 0 | 0 | 0 | NO | 0 | |
| 2 | 23 | X3 | 2 | 0 | 0 | 0 | NO | 0 | |
| 2 | 24 | X3 | 2 | 0 | 0 | 0 | NO | 0 | |
| 1 | 25 | X3 | 2 | 0 | 0 | 0 | NO | 0 | |
| 1 | 26 | X3 | 2 | 0 | 0 | 0 | NO | 0 | |
| 1 | 27 | X3 | 2 | 0 | 0 | 0 | NO | 0 | |
| 6 | 28 | X4 | 33 | 27 | 6 | 0 | YES | 6 | |
| 2 | 29 | X4 | 33 | 25 | 2 | 0 | YES | 2 | |
| 4 | 30 | X4 | 33 | 21 | 4 | 0 | YES | 4 | |
| 20 | 31 | X4 | 33 | 1 | 20 | 0 | YES | 20 | |
| 4 | 32 | X4 | 33 | 1 | 0 | Partial | 0 | NO | 0 |
| 12 | 33 | X4 | 33 | 1 | 0 | Partial | 0 | NO | 0 |
| 8 | 34 | X4 | 33 | 1 | 0 | Partial | 0 | NO | 0 |
| 1 | 35 | X4 | 33 | 0 | 1 | 0 | YES | 1 | |
| 2 | 36 | X4 | 33 | 0 | 0 | 0 | NO | 0 | |
| 1 | 37 | X4 | 33 | 0 | 0 | 0 | NO | 0 | |
| 2 | 38 | X4 | 33 | 0 | 0 | 0 | NO | 0 | |
| 2 | 39 | X4 | 33 | 0 | 0 | 0 | NO | 0 | |
| 1 | 40 | X4 | 33 | 0 | 0 | 0 | NO | 0 | |
| 8 | 41 | X4 | 33 | 0 | 0 | 0 | NO | 0 | |
| 10 | 42 | X4 | 33 | 0 | 0 | 0 | NO | 0 |
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.
9 Replies
Hi 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.
FBergamaschiPreviewhere is the explanation.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.
Hi Sasi_2108 ,
Excel relies on row-by-row recursion, a calculation style that DAX does not support. In DAX, the same outcome is achieved by shifting away from a procedural approach and instead using a set-based, cumulative evaluation. Rather than referencing the immediately preceding row, DAX evaluates all rows up to the current one within the same key and defined order, and derives the remaining quantity from that accumulated context. As soon as the cumulative demand exceeds the available priority quantity, the allocation naturally falls to zero. As a result, the helper columns often needed in Excel, such as running totals, flags, or partial indicators, are no longer necessary in DAX, because these outcomes emerge implicitly from filter context. In short, Excel-style recursion is replaced by cumulative filtering in DAX, producing the same results without recursion.
Hi SShirivolu, thanks for your efforts and response. if you see the excel results of the below columns, values should exactly match with the table I provided. basically the business wants to split ADD Priority Qty across the different sales items based on Pre-alloc quantities and running quantities so its like distributing. for example, in the first key, 46 is ADD Priority Qty if you sum Total Add Priority same 46 would be there which means 46 is distributed across 11 rows.
Flag and Qty to add Priority Partial Qty Total Add Priority Hi Sasi_2108 ,
This can be achieved in Power BI without true recursion by translating the Excel-style row-by-row depletion into ordered cumulative logic. After establishing a clear row sequence within each Key, the model calculates how much Pre-alloc quantity has already been consumed by prior rows, which allows it to determine how much of the Add Priority pool remains before each row is processed. Each row then receives the lesser of its Pre-alloc amount or the remaining pool, ensuring allocation follows the correct order and automatically stops once the pool is exhausted. The final 'Total Add Priority' values match the Excel results exactly, with no row exceeding its Pre-alloc and the total distributed per Key equaling the original Add Priority quantity, as demonstrated in the attached PBIX file.
Please find the attached .pbix file for your reference.
