Calculate the inventory risk based on batch expiration date and monthly forecast data

[Anonymous]

Dear community, 

I have two tables, one is the batch inventory data with expiration date, notice that I only include one product as example.  the sample data(excel raw data and powerbi file is in the dropbox link below)

data..https://www.dropbox.com/sh/7krrycqcb9twd2c/AADTMrEib_1lUI0o8TiB3u5ta?dl=0

Expect Result

I would like to calculate the stock that could not consume before the expiration date. Please notice that the main key shouldbe product id+plant id.  

For example product 1000601 with plant 3094 has total 5 different batch #, each batch has different expiration date.  the first one 1168 qty will be expired in March 13 2025, the second one 900 will be expired in Mar 23, 2025. From the forecast table I can get the total forecast qty from the first month to the month that the product will be expire. In this case, I need to calculate forecast qty from Jan 2022 till Mar 2025 to get the total rolling forecast = 2410 , so the total 1168 is not on risk, but the second row 900 pieces also in March, the total march stock should be 1168+900 =2068, then compare it with 2410. Use the rule is first come first out policy to calculate next batch status. In May 2025, there are 756 qty will be expired, total runnin forecast is 2429, but in 2429 I need minus all forecast till March because March will be fillfulled by Martch batch inventory. 

expire result in table

Batch Number	Batch Expiration Date	SAP Material Number	SAP Plant ID	Unrestricted Stock	Unrestricted Stock Value	RunningForecastTotal	risk qty	is risk?	excel formula for H
1C01734	3/13/2025	1000601	3094	1,168	6,395	2410	-	no
1C03003	3/23/2025	1000601	3094	900	4,928	2410	(342)	no	=(SUM($E2:$E3)-$G3)	In March two batches will be exipred
1E02765	5/25/2025	1000601	3094	756	4,139	2429	737	yes	=$E4-($G4-$G3)	first come first out, 2429 includes all forecast before May, but the March forecast has been filled by March batc
1E04313	6/2/2025	1000601	3094	1,944	10,644	2438	-	no
1F03240	6/28/2026	1000601	3094	204	1,117	2438	2,139	yes	=(SUM($E5:$E6)-($G6-$G4))	in Jun two batches will be expired

 attached is the sample in dropbox. data..https://www.dropbox.com/sh/7krrycqcb9twd2c/AADTMrEib_1lUI0o8TiB3u5ta?dl=0

Would be very appreciate if I could get a good answer, thank you so much!

[Anonymous]

Hi @Anonymous  I am building a Freshness Tracker Report with the same purpose, did you manage to find a solution? 

Thank you!

[phatlewt4u]

I'm also interested to know if and how this problem can be resolved since I wanted to build similar report myself. 

[Anonymous]

Hi @Anonymous ,

First, I create a column in Foreast table to get RunningForecastTotal column

Total column = CALCULATE(SUM('forecast data'[Value]), FILTER('forecast data', [SAP Plant ID]=EARLIER('forecast data'[SAP Plant ID]) &&  [forecast  date]<=EARLIER([forecast  date])))

RunningForecastTotal = LOOKUPVALUE('forecast data'[Total column],'forecast data'[SAP Plant ID],[SAP Plant ID],[forecast  date].[Year],[Batch Expiration Date].[Year],[forecast  date].[MonthNo],[Batch Expiration Date].[MonthNo])

So does the expected output need to consider whether it is left before,right?

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous 
I got that step, but I dont know how to calculate the next step. which is to identify the risk. 
in your example the first 2 rows, the forecast value all belong to March, but we have 2 batches in march, one is 1168 qty, the other one is 900 qty. how to calculate the first come first out measure?