Forum Discussion
Predict inventory to expire compared to forecasted demand
Hello,
I don't know how to do this other than manually, with a calculator (I'm just that dumb...)
I want to know how much of my inventory is in risk of spoilage due to it reaching its expiration date. I have an expected consumption (demand forecast) for each product, and if there were nothing else I would just multiply the expected monthly consumption times the remaining shelf life (in months) of the product and then substract that from the inventory on hand. But for each product I have multiple batches with different expiration dates.
I have an example:
For some product P:
| Batch | Stock (units) | Shelf life (months) | Monthly consumption (units p/mo) | At risk stock |
| a | 1 | 3 | 34 | 0 |
| b | 47 | 4 | 34 | 0 |
| c | 104 | 4 | 34 | 16 |
| d | 221 | 7 | 34 | 119 |
| e | 56 | 9 | 34 | 0 |
The first month I need 34 units. I use the unit in batch a, plus 33 units of batch b. Then I have only 14 units of batch be left. Second month I need another 34 units, then I consume the 14 I had from batch b plus 20 from batch c. The next two months I need 68 units and I take them from batch c. But the 5th month I can't use remaining 16 units because they're spoiled. So I begin using the 221 units from batch d. And in two months time I'm left with 119 units remaining from batch d that got spoiled.
Could something like this be generalized in DAX to be carried out with several hundred products and several thousand batches?
Thanks.
8 Replies
v-frfei-msftCommunity Support
Hi Fbecerra ,
In you sample data, seemed that the expiration dates are missing. Could you please share all the necessary data to me? If you don't have any Confidential Information.Please upload your files to One Drive and share the link here.
Regards,
FrankHi v-frfei-msft
I didn't think the exact dates were relevant, since I calculate the remaining shelf life as the difference between today and the expiration date. But if you think it's helpfull I'll add it.
Also, I will add another case (think of it as another product). Because I either get it working with one case or the other, but not both...
And also, I think I should clarify something: I don't think this to be possible independent of some sort of assumptions, mine were that (1) the inventory is used FEFO (first expiring, first out) and (2) if there are more than one batch with the same shelf life, the smaller batches get consumed first -in my example, first batch (b) and then batch (c) even if both have 4 months left-. From this assumptions, the one I'm least certain of is the second, it isn't really necesary in real life to be consumed that way, but I thought it would make the analysis easier -it didn't obviusly hehehe...
Here's the update to the sample data:
Product Batch Stock (units) Shelf life (months) Monthly consumption (units p/mo) At risk stock Expiration date P1 a 1 3 34 0 26/6/19 P1 b 47 4 34 0 26/7/19 P1 c 104 4 34 16 26/7/19 P1 d 221 7 34 119 26/10/19 P1 e 56 9 34 0 26/12/19 P2 A 21 5 4 1 26/8/19 P2 B 69 16 4 25 26/7/20 P2 C 115 28 4 67 26/7/21 P2 D 3 46 4 0 26/1/23 Well,
I think I've found a solution... but in Excel, not in DAX. I'll post the solution here and hope somebody helps me with the translation to DAX.
The sample data is this:
Material # Stock (units) Shelf Life (mo) Monthly Demand Stock at risk (result) 4018958 1 3 34 0 4018958 47 4 34 0 4018958 104 4 34 16 4018958 221 7 34 119 4018958 56 9 34 0 4019543 405 4 11 361 4019543 77 10 11 11 4019543 3,651 20 11 3,541 4019543 130 45 11 0 4019547 21 5 4 1 4019547 69 16 4 25 4019547 115 28 4 67 4019547 3 46 4 0 The excel Formula goes like this (assuming the table headers starts in A1):
I entered the first row's value manually (0 as I see there's only one value). From the second row onwards the formula is:
=IF( (B2 * C2) < SUMIFS( $B$2:B3, $A$2:A3, A3) - SUMIFS($F$2:F2, $A$2:A2, A3),
SUMIFS( $B$2:B3, $A$2:A3, A3) - SUMIFS($F$2:F2, $A$2:A2, A3) - (B2 * C2),0)
Please note that the first (blue) SUMIFS sums all the values from the beginning of the table until the current row, but the second (purple) SUMIFS sums only the values from the beginning of the table until the row before the current one.
I think this would read like "if the demand over the remaining shelf life is less than the Stock on Hand less the Already spoiled stock, then the spoilage is the Stock on hand less the Already spoiled stock less the expected demand, otherwise is 0".
This formula works only if I sort the table by Material, then by Shelf Life and last by Stock.
I believe I could do this with DAX more efficiently (computationally-wise, since I have several thousand rows), and without the need to sort the table in that specific order... But I don't know how.
I also think it should be a calculated column, because then I want to use the results to clasify each row: weather its 0 or not.
Thanks in advance!
Any update on the solution?
Hi, no relevant update. I have not yet found a solution using DAX. Recently I've started working in a python script to do the trick.
