Inventory Estimation where Material Fcst will be subtracted from batches in order of expiry date

[jeffrey_fuji]

Hi,

I am trying to do an inventory estimation calculation:  Current Inventory - Forecast = Estimated Inventory. 

The forecast is  done per material.  The inventory of a material is allocated over several batches, that have an expiration date. 

I currently have the calculation on material level. 

However,I want to do a calculation on batch level: where the Forecast will be subtracted from the batches in order of their  expiration date.

Below you will find the desired situation, how it currently looks in Power BI and some screenshots of the data set and model.

Hopefully someone knows a solution. Thanks!

Desired situation

Forecast dataStock dataModelCurrent situation

[v-shex-msft]

HI @jeffrey_fuji,

Can you please provide more about your measure formulas? I'm not so clear for your measure formulas.

Regards,

Xiaoxin Sheng