Return first date with zero inventory

Question

Hi all,&nbsp;&nbsp;I need to find the first date our sales location have zero inventory of a product.&nbsp;&nbsp;I have tried every possible way to calculate this.&nbsp;&nbsp;So wanted result is this:&nbsp;&nbsp;I nedd to get the date when inventory reaches 0.&nbsp;How do I create a measure or calculated column that gives me the running inventory for every date? Or is this achievable with a measure?&nbsp;Which formula do I use to get the date? I have tried&nbsp;&nbsp;first date below 0 = MINX(FILTER(Table, [Measure running inventory] &lt;= 0),Table[Date])&nbsp;But this returns the first date of activation&nbsp;Please help! 🙂

rfigtree · Answer

=calculate(min(table[date]),filter(all(table),table[runninginventory]&lt;=0))

rfigtree · Answer

running=calculate(sum([distributed]) - sum([activated]) - sum([returned]), filter(all(table), [date] &lt;= earlier([date]))

rfigtree · Answer

hi i hve no idea what the terms distributed, activated and returned mean in relation to adding or subtracting from your stock.I can only suggest checking this, i have assumed distributed adds to your stock and activated and returned reduce your stock. if that is wrong please amend the + and -'s accordingly.running=calculate(sum([distributed]) - sum([activated]) - sum([returned]), filter(all(table), [date] &lt;= earlier([date]))&nbsp;alternatly i could suggest your data is incomplete, your initial stock on hand is not being taken into consideration.&nbsp;there is nothing wrong with the concept of my formula, i use it all the time at work.i too analyse invetory and forecast inventory.

anonymous · Answer

Thanks, but I don't have the runninginventory as a column. I dont understand how to calculate it. If i solve that everything else should fall in place.&nbsp;&nbsp;Any suggestions?&nbsp;

anonymous · Answer

Hi,&nbsp;rfigtree&nbsp;&nbsp;Thanks but that didn't really solve my problem.&nbsp;First distribution for 7 nights kiosk is on the 1.10.2020. on 14.10.2020 inventory should be 6, instead I get the first product activation. So the first date formula returns the dates for every activation, not when inventory is &lt;= 0.&nbsp;&nbsp;Any suggetions? I really need to solve this puzzle 🙂&nbsp;Thank youChrister

Forum Discussion

Return first date with zero inventory

5 Replies

Related content

Inventory and Stock balance calculation

Inventory Forecast

Inventory Management Dashboard

Inventory opening closing

Inventory Management Dashboard