DAX Calculate Inventory Expiry Risk

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

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

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...

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

Forum Discussion