Forum Discussion

jeeviscarozza's avatar
jeeviscarozza
Regular Visitor
3 years ago

Running Total by Lot Number

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
Need Help

5 Replies

    • jeeviscarozza's avatar
      jeeviscarozza
      Regular Visitor
      3 years ago

      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's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

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

  • jeeviscarozza's avatar
    jeeviscarozza
    Regular Visitor
    3 years ago

    Here you go

    Lot Expiration DateProduct lot numberQtyLot Expiration DateProduct item numberIntended Output
    30-Nov-241341201841030-Nov-24134110
    30-Nov-241341201641030-Nov-24134120
    30-Nov-241341201621030-Nov-24134130
    30-Nov-241341201651030-Nov-24134140
    30-Nov-241341201661030-Nov-24134150
    30-Nov-241341201671030-Nov-24134160
    30-Nov-2413412017310030-Nov-241341160
    30-Nov-241341201831030-Nov-241341170
    30-Nov-241341201631030-Nov-241341180
    30-Nov-241341201851030-Nov-241341190
  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    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! ❤️