Measure to estimate loss of sales because of stock out

[Trolleri]

Hello all,

I have a complex problem that i have been struggling with to find a solution, so i hope someone here can help me out with this.

I have a date table and 2 fact tables, one with sales and one with inventory.

We have multiple stores and each store get one entry per day in the inventory table for each product they have in stock, if they dont have a product in stock there is no entry for that, so no "0 stock" entry.

Likewise the sales table only get transactons in it, so there is no "0 sales" entrys there either, just missing rows for the product if it didnt sold that day.

What i would like to do is

1:  check when a store have a missing entry for a product in both of those tables, to confirm a stockout that day

2: estimate missing sales due to that stockout based on the sales before and after the stockout occured.

Sample of sales table:

sale_date	product_id	quantity	store_id
2019-01-01	1	10	1
2019-01-01	1	11	2
2019-01-01	1	10	3
2019-01-01	2	7	1
2019-01-01	2	13	2
2019-01-01	3	11	2
2019-01-01	3	10	3
2019-01-02	1	1	1
2019-01-02	1	2	2
2019-01-02	1	8	3
2019-01-02	2	10	1
2019-01-02	2	11	2
2019-01-02	3	8	2

Sample of inventory table:

date	product_id	quantity	store_id
2019-01-01	1	30	1
2019-01-01	1	20	2
2019-01-01	1	35	3
2019-01-01	2	20	1
2019-01-01	2	25	2
2019-01-01	3	22	1
2019-01-01	3	23	2
2019-01-01	3	22	3
2019-01-02	1	20	1
2019-01-02	1	9	2
2019-01-02	1	25	3
2019-01-02	2	13	1
2019-01-02	2	12	2
2019-01-02	3	12	2

So the following products and dates should be marked as stockout, could be a measure, a column or a table:

date	product_id	store_id	Stockout
2019-01-01	2	3	TRUE
2019-01-02	2	3	TRUE
2019-01-02	3	1	TRUE
2019-01-02	3	3	TRUE

Is this possible to achieve?

[v-lionel-msft]

Hi @Trolleri ,

Maybe you can try to do like this:

Number_store = 
CALCULATE(
    DISTINCTCOUNT(Inventory[store_id]),
    ALLEXCEPT(
        Inventory,
        Inventory[date], Inventory[product_id]
    )
)

Sum_store = 
CALCULATE(
    SUM(Inventory[store_id]),
    ALLEXCEPT(
        Inventory,
        Inventory[date], Inventory[product_id]
    )
)

Store_id(measure) = 
VAR x = 
CALCULATE(
    MAX(Inventory[store_id]),
    ALL(Inventory)
)

RETURN
SWITCH(
    [Number_store],
    x, BLANK(),
    x-1, 6-[Sum_store],
    x-2, 6-[Sum_store]
)

Stockout = 
IF(
    [Store_id(measure)] <> BLANK(),
    "True",
    BLANK()
)

Best regards,
Lionel Chen

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

[Mariusz]

Hi @Trolleri 

Sure, Please see the attached solution with the below Measure that later is used to filter the table 

check = 
VAR __date = MAX( 'calendar'[Date] ) <= CALCULATE( MAX( sales[sale_date] ), ALL( sales ) )
RETURN 
    INT( ISEMPTY( sales ) && ISEMPTY( inventory ) &&  __date )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Trolleri]

Hi @Mariusz ,

The formula works, but it always returns 0.

Im not sure what it is supposed to return?

[Mariusz]

Hi @Trolleri 

Have you checked the attached file?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Trolleri]

Hi @Mariusz ,

Now i found out why it didnt worked for me, you had a +0 in your sums so now its displaying correctly for me in that type of visual, thank you.

But if i want to count those rows where the filter is 1, how do i do that?

I want to figure out how many days in % that had stockouts for each product, i have tried to count rows with that filter but it doesnt return anything if i set the filter to 1.

[Mariusz]

Hi @Trolleri 

Please see the attached file with adjusted code to return Total Count 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[amitchandak]

@Trolleri , if @Mariusz 's solution does not work, the mark both of us @. We will check again.

[amitchandak]

On 2019-01-01, how do we know sale is lost, when these no record in sales. Because there is no inventory record for that day ??

@Trolleri 

Something like

except(
crossjoin(distinct(sales[date]),distinct(sales[product_id]),distinct(sales[store_id])),
selectcolumns(inventory,"date",inventory[date],"product_id",inventory[product_id],"store_id",inventory[store_id]
))

[Trolleri]

Hi @amitchandak, ,

Thank you for your swift reply!

Yes, we assume sale is lost because there is no inventory record that day.

Unfortunately that formula doesnt seem to work, i get this error:

"The expression refers to multiple columns, Multiple columns cannot be converted to a scalar value"