DAX Calculate Inventory Expiry Risk

[nardcox]

I've been searching a lot but can't seem to find an answer. I'll try to keep it brief, concise and complete.

• I work in a warehouse
• We store goods on locations
• Some of these goods have an expiry date
• I want to calculate what the risk of expiring is for these items per location

Sound simple enough right, but

• We have a fix sequence of locations that this stock will be allocated, so I have to take that into account

Example data for a single item

Sales Qty per day:	2,43
Sequence	Exp Date	Stock Qty	Expiry Qty (Desired output)
1	28.02.2023	119	0
2	31.05.2023	121	0
3	31.05.2023	806	620
4	30.06.2023	42	0
5	28.02.2023	372	372
6	31.05.2023	810	810
7	28.02.2023	218	218
8	30.06.2023	928	898

I can't get the table looking properly so I've added a prntscrn below as well.

Really hoping someone can help. The closest I've gotten was this, the last qty (631) is incorrect.

Maybe the formula was just lucky getting the rest correct, haven't checked my other examples yet.

Old Exp Stock Qty =

var days_till_today = stock_exp[Date]-today()

var days_between_dates = MAX(0,DATEDIFF(LOOKUPVALUE(stock_exp[Date],Stock_exp[Sequence],Stock_exp[Sequence]-1),stock_exp[Date],DAY))

var daily_sales = 2+(43/100)

var cum_physical = CALCULATE(SUM(Stock_exp[Stock]),FILTER(Stock_exp,stock_exp[sequence]<=EARLIER(Stock_exp[Sequence])))

var cum_sales = days_till_today*daily_sales

return

IF(Stock_exp[Sequence]=1,

    MAX(0, ROUNDDOWN( stock_exp[Stock] - cum_sales , 0) ),

    MAX(0,

        ROUNDDOWN(

            MIN( cum_physical - cum_sales ,

                stock_exp[Stock] - days_between_dates * daily_sales )

                ,0)

        )

    )

EDIT: I just found this topic, gonna read up on it now...

https://community.powerbi.com/t5/Desktop/Predict-inventory-to-expire-compared-to-forecasted-demand/m...

EDIT: Unfortunately no solution in the above topic, it was also slightly simpeler because that one works FEFO which I don't 😞

EDIT: Neither in any of the following topics

https://community.powerbi.com/t5/Desktop/Inventory-based-on-Expiry-Dates-FIFO/td-p/2086641

https://radacad.com/dax-inventory-or-stock-valuation-using-fifo

https://community.powerbi.com/t5/Desktop/Inventory-at-risk-of-expiring/td-p/1615217

https://community.powerbi.com/t5/Desktop/Projecting-Inventory-to-Expire-based-on-forecast/td-p/96725...

https://community.powerbi.com/t5/Desktop/Excess-Inventory-Calculation/m-p/355483

https://community.powerbi.com/t5/Desktop/Inventory-calculation-Based-on-expiry-date/m-p/769510

[PaulDBrown]

Check out this thread which seems to tackle a similar problem

https://community.powerbi.com/t5/Desktop/Calculate-Future-Date-of-Restock/td-p/2918589 

[nardcox]

@PaulDBrown, thanks for the link. I've read the topic but for that one you calculated a ROP (ReOrderPoint), I've been trying to understand the logic (which I don't), which makes it impossible for me to transfer that knowledge to my own case. Aside from not understanding I'm not sure if the logic in the other topic is the same as what I'm trying to do here.

[PaulDBrown]

Can you please go trhough the logic of the Expiry Quantity calculation?

[nardcox]

@PaulDBrown, thanks for taking the time and apologies for the late response, had a long weekend.

The question I'm basically trying to answer is: how many items do we have a potential expiry risk aka how many items do we might have to throw away?

The sequence indicates the sequence in which the locations are being emptied.  So I have to check location by location if I can sell the items before the date is reached.

Example

• Item: A
• Sales per day: 1
• Number of locations: 4
• Location 1 : 10pcs, expires 17 dec 2022
• Location 2 : 5 pcs, expires 30 dec 2022
• Location 3 : 20 pcs, expires 5 jan 2023
• Location 4 : 3pcs, expires 3 jan 2023

Location 1 : I have 5 days of sales left (17 dec - today 12 dec = 5). I sell 1 per day meaning I can sell 5pcs in the coming 5 days. I have 10pcs on stock meaning I will have to throw away 5 pcs. So the expiry qty is 5 pcs.

Location 2 : I have 13 days of sales between 17 dec and 30 dec. I sell 1 per day meaning I can sell 13 pcs, however I only have 5 so no risk on this location, I could actually sell 8 more in this time period.

Location 3 : I have 7 days on sales between 30 dec and 5 jan. I sell 1 per day meaning I can sell 7 within this period. However the last location only lasted untill (17 dec + 5 days = 22 dec). Meaning I can sell some of the items on location 3 in this periode. From 22 dec to 30 dec are 8 days, meaning I can sell 8 units from this location also. Meaning to expiry risk is 20pcs - 8pcs - 7pcs = 5pcs. So I will have to throw away 5pcs at 5 jan.

Location 4 : Location 3 will already have expiring items meaning location 4 will be completely expired since that date is before the date in location 3 but I only starting picking from location 4 after 3 (due to the sequence) meaning this complete location will potentially expire.

I've made a small table to show the inventory levels during this time period, maybe that helps to clarify it.

[PaulDBrown]

See if this is what you need:

A temporary measure to get the calculation

Expiry Risk Temp = 
VAR _Days = DATEDIFF(TODAY(),MAX(Stock_exp[Date]), DAY)
VAR _Sales = _Days * 2.43
VAR _Qty = SUM(Stock_exp[Stock]) - _Sales
RETURN
IF(_Qty <0, ABS(_Qty))

And to get the total

Expiry Risk Total = 
SUMX(Stock_exp, [Expiry Risk Temp])

Sample PBIX file attached 

[nardcox]

@PaulDBrown, unfortunately that doesn't work.

For example the first line. I have 119 pcs, 105 days left. 105 days left * 2.43 = 255 sales possible. I only have 119 pcs. Meaning I can sell all of them before they expire, meaning the risk will be 0 since I can sell all of them.
The risk you calculated is actually higher than the stock I have, which isn't possible because I can't scrap more items than I actually have.

[PaulDBrown]

@nardcox 

Apologies, you are absolutely right. I was calculating the "extra sales" as opposed to the Expiry Quantity.

Try this for the temp measure:

Expiry Risk Temp = 
VAR _Days = DATEDIFF(TODAY(),MAX(Stock_exp[Date]), DAY)
VAR _Sales = _Days * 2.43
VAR _Qty = SUM(Stock_exp[Stock]) - _Sales
RETURN
IF(_Qty > 0, _Qty)

[nardcox]

@PaulDBrown, unfortunately that's also incorrect.
Till 31 May = 169 days * 2.43 = 410 pcs of sales
I have 119 + 121 + 806 = 1046 - 410 = 636 pcs with exp risk and not 395.

You calculate it per line, but the calculations needs to take the results from all previous lines into account as well.

[GGerritsen]

Hello,  I am trying to do the same,  just simple 1 location for now.

Inventory - expiry date,  calculate what my risk is going to be, based on expected sales.  

Did you ever get a solution for this ?

[PaulDBrown]

So what determines the cumulative value? The absolute chronological order or the sequence order? If it's the sequence order, what is the calculation for example for sequence 1->4?

[nardcox]

@PaulDBrown, the cumulative value is dictated by the sequence in which the locations will be empties, aka the Sequence column.

Just image you have 8 locations in front of you, all have the same item. Each location has an expiration date and you start selling from the most left locations and work your way to the right. You might encounter a location with stock which you can't sell anymore because you have reached the expiry date and you need to scrap those items and move to the next location.

Regarding your question regarding the 1 > 4 sequence.

Location 1 has 119 pcs. Till 28.03 I can sell 250pcs, meaning I can sell all items before this date is reached, actually I could sell even (250 - 119=) 131 more pcs.

Then I start from location 2. Here are 121 pcs. I can sell all of these even before the exp date from location 1 is reached. Till this point in time I could have sold 406 pcs ((31.05 - today ) * 2.43). I've only sold 240 (191 + 121) so far.

Then I move to location 3. Same date as locaiton so meaning I can still sell 166 (406 - 240) from this location. However I have 806 on this location meaning I need to scrap 640 pcs (806-166).

Then I move to location 4. All previous locations have been empties by selling or expiry. So from the exp date of location 3 till the exp date of location 4 I can sell (30 * 2.43=) 72 pcs. I only have 42 so I can sell all of them before the exp date is reached, I could even sell (72 - 42 =)30 more.

Location 5&6&7. These dates are before the date of location 3 for which I scrapped the pcs I couldn't sell, so I need to scrap all of these as well.

Location 8 is the same as location 4. I was still able to sell 30 more before this date was reached so I will sell those 30 and for the rest there is no time left since the exp date will be reached before I can sell them. Meaning here I have to scrap 928 - 30 pcs meaning I will have to scrap 897pcs on this location.

[nardcox]

@v-luwang-msft, sure, see the link below.

Example.pbix

Please let me know if this is not working.

[v-luwang-msft]

HI @nardcox ,

Pls share your pbix file ,and remember to remove confidential data.

Best Regards

Lucien