Calculate Days Of Zero Stock in Power BI

Posted on by Summa Lai

Calculate Days Of Zero Stock

Background:

I am building a supply chain dashboard 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)

  1. Date that stock hit zero
  2. length of time stock was at zero before goods in stock was received.

My problem is I cannot figure out how to…

  1. 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.
  2. 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.
Capture

Solution:

Instead of using if statements you should be using either…

COUNTX

or

CALCULATE( COUNTROWS( .... ),
     FILTER( ALL( .......),
            Some logic here for every row )

The context of you calculation is very important here also.

If you are looking for answers in the card visualization this means that basically there is no context other than what is coming from the slicers in your report. But if you are able to select specific products in a slicer this bring a new element to the calculations.

So for example you probably want something like this.

COUNTX(
      SUMMARIZE( StockTable, DateColumn, ProductNameColumn,
              "StockAmount", [CTA QTY] ),
                          [StockAmount] = 0 )

Something like the above I think will get you there.

Other than this would need to see an example file to assist any further as there could be other things to consider, but this is my best guess based on the information provided so far.

Calculate Days Of Zero Stock – Power BI Inventory Management Insights

Ref: https://forum.enterprisedna.co/t/count-zero-stock-occurrences-and-time-product-is-out-of-stock/3603