I am building a supply chain dash board at our company and a big problem we are having is STOCK OUTS.
I have a table of over 5 million rows that contains all our SAP movement data inside inbound goods in and outbound issues to production.
From here i have created the cumulative / Running totals measures very easily from the Enterprise DNA superb videos using (CTA = Cumulative Total All (All meaning all filter))
CTA QTY = CALCULATE([Total Quantity], FILTER( ALL( Dates ), Dates[Date] <= MAX( Dates[Date] )))
//All Statement takes all dates from start of time upto last date.
Now I see some information such as (See screen shot of my table)
- Date that stock hit zero
- length of time stock was at zero before goods in stock was received.
My problem is I cannot figure out how to…
Count the unique amount of times that product hit Zero stock? i have created a IF statement, N Stock Outs = IF( [CTA QTY] <= 0, “1”, “0”) as this creates a 0 and 1 for me but i dont know really where i go from here ? to get it to uniquely identify an exact count of a stock out date.
Create the N of days with zero stock. Potentially the formula above gives me a consecutive run of 1s i could some how count all those up and get a total but i dont know how to do it.
Is it possible one of you clever people can show me how to do it?