Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fbecerra
Frequent Visitor

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
a1334

0

b474340
c10443416
d221734119
e569340

 

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 8
v-frfei-msft
Community Support
Community 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,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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.

Hi @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:

 

ProductBatch Stock (units)Shelf life (months)Monthly consumption (units p/mo)At risk stockExpiration date
P1a1334026/6/19
P1b47434026/7/19
P1c1044341626/7/19
P1d22173411926/10/19
P1e56934026/12/19
       
P2A2154126/8/19
P2B691642526/7/20
P2C1152846726/7/21
P2D3464026/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 DemandStock at risk (result)
401895813340
4018958474340
401895810443416
4018958221734119
4018958569340
4019543405411361
401954377101111
40195433,65120113,541
401954313045110
401954721541
40195476916425
401954711528467
401954734640

 

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.

mb0307
Responsive Resident
Responsive Resident

@Fbecerra Did you find the solution in Power BI?  if yes then possible to share please?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.