Count number of days at 0 balance

[SIA3720]

Hi,

I have a table listing he running balance of stock items. Over 1000 items. A new line is added to the table whenever there is movement in the stock quantity i.e. when the stock increases or decreases. There is not a movement for every item everyday. 

This means that if an item reaches a 0 balance, a new line will not appear until the next time the stock is replenished. 

The issue I have is I need to calculate the % stock out rate per month.

I toyed with the idea of filling in all the missing dates between movements and filling down the balances but found this super slow, although I have done it to get a closing monthly balance per item.

Sample data:

Date	Item	Running_stock_balance
01/09/2023	A	150
01/09/2023	C	14
02/09/2023	A	110
03/09/2023	B	43
04/09/2023	A	65
05/09/2023	A	40
07/09/2023	A	0
08/09/2023	B	0
10/09/2023	A	200
10/09/2023	C	13
14/09/2023	A	180
15/09/2023	A	150
15/09/2023	C	12
17/09/2023	A	140
18/09/2023	A	130
21/09/2023	B	100
23/09/2023	A	110
26/09/2023	B	96
28/09/2023	B	95
29/09/2023	C	17
30/09/2023	A	100
30/09/2023	C	16

Required Output: (Sum of number of items per day that had a stock out / (total items*days in month))

Month	%_stockout
Sep-23	21%

Any help would be much appreciated 🙂

Thanks

[SIA3720]

Hi Ahmed, 

To get to 21%, I summed the number of days each item had 0 stock, which totalled 19. I then divided by 90 (30 days * 3 items - the maximum number of days each item could have been in stock. 

A table may explain it better:

Workings:

Date	Num_Stock_outs	Count_Max_Stock_Items	Count_Actual_Stock_Items
01/09/2023	0	3	3
02/09/2023	0	3	3
03/09/2023	0	3	3
04/09/2023	0	3	3
05/09/2023	0	3	3
06/09/2023	0	3	3
07/09/2023	1	3	2
08/09/2023	2	3	1
09/09/2023	2	3	1
10/09/2023	2	3	1
11/09/2023	1	3	2
12/09/2023	1	3	2
13/09/2023	1	3	2
14/09/2023	1	3	2
15/09/2023	1	3	2
16/09/2023	1	3	2
17/09/2023	1	3	2
18/09/2023	1	3	2
19/09/2023	1	3	2
20/09/2023	1	3	2
21/09/2023	1	3	2
22/09/2023	1	3	2
23/09/2023	0	3	3
24/09/2023	0	3	3
25/09/2023	0	3	3
26/09/2023	0	3	3
27/09/2023	0	3	3
28/09/2023	0	3	3
29/09/2023	0	3	3
30/09/2023	0	3	3
Total	19	90	71

Results:

	Sep-23
Stockout%	21%
StockAvailability%	79%

Thanks 🙂

[Ashish_Mathur]

Hi,

I do not see any connection between the table that you shared in the first post and this one.  Which of these 2 tables is the input table and which are the input columns?

[SIA3720]

Hi,

The sample data in the first post is the input data showing the running stock balance per item. 

The workings table in my second post shows a count of items with 0 stock for each day, a count of all items per day, and a count of items with available stock per day.   I created this table to demonstrate how I performed to stock out calculation. 

thanks

[v-rzhou-msft · Accepted solution]

Hi @SIA3720 ,

According to your statement, I think the sample you shared before is not all data.

Please try code as below to create a measure.

%_stockout =
VAR _GENERATE =
    GENERATE ( 'Calendar', VALUES ( 'Table'[Item] ) )
VAR _Findstockout =
    FILTER (
        _GENERATE,
        CALCULATE ( SUM ( 'Table'[Running_stock_balance] ) = BLANK () )
    )
VAR _TOTAL =
    DAY ( CALCULATE ( MAX ( 'Calendar'[Date] ) ) ) * DISTINCTCOUNT ( 'Table'[Item] )
RETURN
    DIVIDE ( COUNTROWS ( _Findstockout ), _TOTAL )

Best Regards,
Rico Zhou

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

[Ahmedx]

explain how you got 21%
from your data show what was divided by what