Calculating out of stock - for products over a period of time, with varied frequency upates

[marzwrob]

I have a data set which can be seen in a matrix format like this: (showing in matric because it is easier to visualise that updates on stock are not regular):

Each product is receiving updates and varied frequency levels.

What I want is to calculate how many days in a period of time (e.g., a month) each product was out of stock.

The logic would go something like:

if a stock level changed on Day X to 0, or a negative number, then use this day as a first day of out of stock. If there is another record inserted for this product, and is still 0 or another negative, still use as out of stock. If this new entry is positive, count it as in stock.

Then calculate over a month how many days each product was out of stock. 

Someone help, please? 

Sample data: 

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[v-tianyich-msft · Accepted solution]

Hi @marzwrob ,

I made simple samples and you can check the results below:

Measure = var _t = ADDCOLUMNS('Table',"Count",COUNTAX(FILTER(ALL('Table'),[ID]=EARLIER([ID])&&[Value]<=0),[Date]))
RETURN MAXX(_t,[Count])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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