topic Re: Return first date with zero inventory in DAX Commands and Tips

Return first date with zero inventory

Anonymous — Thu, 04 Mar 2021 10:46:30 GMT

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! 🙂

Re: Return first date with zero inventory

rfigtree — Thu, 04 Mar 2021 11:01:50 GMT

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

Re: Return first date with zero inventory

Anonymous — Thu, 04 Mar 2021 14:37:36 GMT

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?

Re: Return first date with zero inventory

rfigtree — Thu, 04 Mar 2021 19:13:45 GMT

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

Re: Return first date with zero inventory

Anonymous — Fri, 05 Mar 2021 07:48:42 GMT

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

Re: Return first date with zero inventory

rfigtree — Sat, 06 Mar 2021 05:26:49 GMT

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.