Running Total by Lot Number

[jeeviscarozza]

Hi! I am trying to get a running total of our items we have in stock for each item, by adding up the quantity of each consecutive lot. Each item has multiple lots. The product lot number column indicates the lot number, the qty is the quantity. I am getting an error that the quantity cant be used. Each item has a specific quantity in each lot, and I want to add these quantitites. This is my code. Any help?

running totalattempt =

var currentrank = RANKX(ALL('Reno On Hand Inventory'[Product lot number]), [Qty], , DESC, Dense)

var result = CALCULATE([Qty],

    FILTER(ALL('Reno On Hand Inventory'[Product lot number]),

        RANKX(ALL('Reno On Hand Inventory'[Product lot number]), [Qty], , DESC, Dense) <= currentrank

    )

)

return result

[parry2k]

@jeeviscarozza you can easily leverage new WINDOW functions, check this video 

https://youtu.be/VvPcPaVx64A

https://youtu.be/ukZJGUhsXY8

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

[jeeviscarozza]

Here you go

Lot Expiration Date	Product lot number	Qty	Lot Expiration Date	Product item number	Intended Output
30-Nov-24	134120184	10	30-Nov-24	1341	10
30-Nov-24	134120164	10	30-Nov-24	1341	20
30-Nov-24	134120162	10	30-Nov-24	1341	30
30-Nov-24	134120165	10	30-Nov-24	1341	40
30-Nov-24	134120166	10	30-Nov-24	1341	50
30-Nov-24	134120167	10	30-Nov-24	1341	60
30-Nov-24	134120173	100	30-Nov-24	1341	160
30-Nov-24	134120183	10	30-Nov-24	1341	170
30-Nov-24	134120163	10	30-Nov-24	1341	180
30-Nov-24	134120185	10	30-Nov-24	1341	190

[parry2k]

@jeeviscarozza could you please post the sample data with the expected output.

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

[jeeviscarozza]

I am trying to get the running total of the quantity. For example, for this item it should be 10, then 20, then 30, etc. As a bonus, I want this calculation to stop when it reaches the quantitiy needed, which is 240.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.