Return first date with zero inventory

[Anonymous]

Hi all, 

I need to find the first date our sales location have zero inventory of a product. 

I have tried every possible way to calculate this. 

So wanted result is this: 

I nedd to get the date when inventory reaches 0. 

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? 

Which formula do I use to get the date? I have tried 

first date below 0 = MINX(FILTER(Table, [Measure running inventory] <= 0),Table[Date])

But this returns the first date of activation

Please help! 🙂

[rfigtree]

=calculate(min(table[date]),filter(all(table),table[runninginventory]<=0))

[Anonymous]

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. 

Any suggestions? 

[rfigtree]

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

[Anonymous]

Hi, @rfigtree 

Thanks but that didn't really solve my problem. 

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 <= 0. 

Any suggetions? I really need to solve this puzzle 🙂

Thank you

Christer

[rfigtree]

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] <= earlier([date]))

alternatly i could suggest your data is incomplete, your initial stock on hand is not being taken into consideration.

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.