Forum Discussion
DAX Calculate Inventory Expiry Risk
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.
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
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.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)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.