Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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,
Frank
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.
Thats great! Could you pls advise how you were able to achieve using Python?
I need to do something similar.
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!
@Fbecerra, it's a long shot but did you ever find the answer in DAX? I have the same issue with an extra trick that my locations aren't FEFO overall, but only within a certain area, meaning I have a location sequence in which I consume the locations which makes it very tricky. Hope to hear from you, even if you didn't solve it.
@Fbecerra Did you find the solution in Power BI? if yes then possible to share please?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |