Trying to create an out stock in number of days

[NBOnecall]

Hi All, 

I am trying to create an out of stock report for our inventory items based off of a table pulling in on hand levels every day. If there is no on hand or nothing on on order it doesn't add any rows into the table for that item. Below is the table I am using. I can have multiple locations where the item is carried and would like to be able to calculate the out of stock per location. My goal is to have a count of number of days out of stock total, and if itsn't complicated maybe a date range. 

If we look at my table for this item (110518) we can see that starting on March 1st we went from 2 available to 0 available, we didn't get more back into stock until the March 14th. This created an out of stock of 13 days. 

I would like to output a visualization that looked something like this -

The other factor to all of this, is lets say that this item has been out of stock for the past 3 days and hasn't come into stock yet, this would also need to be added to the total out of stock days. Any ideas of how to tackle this?

Thanks, 

Noel 

[NBOnecall]

This is what I have so far. The one thing I still need is to be able to add up the most recent dates that don't have inventory. For example my formula only calculates if there is a row that does have on hand inventory. I will be the first to admit that this might not be the best code, but I tried my best looking through this forum.

6 Out of Stock = if('ns InventorySnapshot'[LocationId] = 6 && 'ns InventorySnapshot'[Available] <> 0, 
VAR temp =
    TOPN (
        1,
        FILTER (
            'ns InventorySnapshot',
            'ns InventorySnapshot'[ItemInternalId] = EARLIER ( 'ns InventorySnapshot'[ItemInternalId] )
                && 'ns InventorySnapshot'[InventoryDate] < EARLIER ( 'ns InventorySnapshot'[InventoryDate] ) 
                    &&  'ns InventorySnapshot'[Available] > 0
                        && 'ns InventorySnapshot'[LocationId] = 6
        ),
        [InventoryDate], DESC
    )
RETURN
    if(DATEDIFF ( MINX ( temp, [InventoryDate] ), 'ns InventorySnapshot'[InventoryDate], DAY )> 0, DATEDIFF ( MINX ( temp, [InventoryDate] ), 'ns InventorySnapshot'[InventoryDate], DAY ) -1, 0),0)

If looking at the picture I need to calculate for location 6, how many out of stock days it has been since May 23rd, since not date since then has received inventory in stock. 

[v-yuta-msft]

@NBOnecall ,

---

I am trying to create an out of stock report for our inventory items based off of a table pulling in on hand levels every day. If there is no on hand or nothing on on order it doesn't add any rows into the table for that item. Below is the table I am using. I can have multiple locations where the item is carried and would like to be able to calculate the out of stock per location. My goal is to have a count of number of days out of stock total, and if itsn't complicated maybe a date range. 

---

Could you show which colunmn contains the value which represent "stock"? (Or maybe Sales or Cost?) In addtion, I have found the ItemID 110518 seems like to be available in Feb, so why does it start from Mar? And how could you achieve 13 days?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NBOnecall]

@v-yuta-msft 

The column available would be what signals out of stock. If it becomes 0 on any day, then it is out of stock. The table that has this data does not get updated if there is nothing Available, OnHand, OnOrder, or InTransit. So between March 1st - 7th there was no activity. And then for March 8th there was nothing on on hand, March 9th, no data updated to the table, Marych 10th -13th nothing available. So with those those two factors (Data not being there for a certain day, and 0 available) I calculated the 13days, March 1st - 13th. Does this answer your question?

Thanks, 

Noel