cancel
Showing results for
Did you mean:
Frequent Visitor

## Return first date with zero inventory

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

5 REPLIES 5
Resolver III

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

Frequent Visitor

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?

Resolver III

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

Frequent Visitor

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

Resolver III

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.

Announcements