Forum Discussion
DAX Calculate Inventory Expiry Risk
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.
Can you please go trhough the logic of the Expiry Quantity calculation?
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.