Best Of

@Chris_Wolman without knowing what your data really looks like is hard for me to picture a full path to follow. In general if what you want is to be able to know what was the inventory at any given point in time (captured using a Variable as @MichelleH suggested), is to have your Raw data being the movements to inventory by date, so you'll have entries whenever things are sold and whenever you acquire new things to sell, and then doing a best mode that aggregates those for any date prior or on the provided variable:

SUM(CASE WHEN movement date <= Variable date ANDmovement type= 'IN' THEN qty END) - SUM(CASE WHEN movement date <= Variable date ANDmovement type= 'OUT' THEN qtyEND)

If you have 2 date variables for the Start and End you'd simply have two Beast Modes that tells you the inventory at each point in time, and the advantage of this is that you can also show how many IN movements were performed between the two dates vs how many movements out (I guess your inventory can go up if you acquire multiple units to sell during the period).

So using your example table, you'd need to do an ETL that would transform that to:

ITEM A 4/15/2023 IN 10
ITEM A 5/5/2023 OUT 2
ITEM A 5/6/2023 OUT 1

You can go even more detailed if besides showing the direction of the movement, you also specify why, like if you have OUT because of sales, OUT because of damaged good, IN by purchases or IN by returns, so this model would be easy to scale to the business needs, but you need to figure out first how to get the individual movement list out (ETL would be your best bet if you don't already have a dataset with this information).

@Chris_Wolman Typically, aggregation functions should be outside of the entire case statement in order to return a single row. However in this case the min date is also causing an issue because you would have an aggregate inside an aggregate (min inside a sum). Based on your use case, I would suggest setting up two date variables for your start and end dates so you can avoid the second aggregation with a beast mode like this:

sum(case when `Date` = `Start Date (Variable)` then `NAV_OnHand` else 0 end)