cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChristerMTvedt
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: 

Merknad 2021-03-04 114143.jpg

 

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! 🙂
5 REPLIES 5
rfigtree
Resolver III
Resolver III

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

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? 

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

Hi, @rfigtree 

 

Thanks but that didn't really solve my problem. 

Merknad 2021-03-05 084506.jpg

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

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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors