Excess Inventory Calculation

[Mack1int]

Good Day to all of you!,

 I am currently building a data model calculating the Obsolete, Slow Moving and Excess inventory for a selection of products.

I am having trouble converting creating a set of equations that work in unison with time intelligence and my calander tables.

I have a situation where I am basically having to put this statement into a formula:

 "When the forecast is not fullfilled by current product batch, I want to to be able to take inventory from the next valid batch. However when that current batch does fulfill the forecast but we have some inventory left over we want to mark this as excess."

I am not even sure if the said calculation is even possible but any assitance would be much appreicated. I have attached a file with my work.

https://www.dropbox.com/s/pygq0hmltsxoeog/Inv_risk_demo_help.xlsx?dl=0

[v-jiascu-msft]

Hi @Mack1int,

To be honestly, I don't know how to get the result of your desired table. Could you please give some calculation processes?

1. Why are the two Forecast the same?

2. How to calculate them?

3. What are those date columns in the Original table?

Best Regards,

Dale

[Mack1int]

Hi @v-jiascu-msft

 Thank you for you reply.

1. The forecasts being the same was my mistake, I have now updated the tables.

2.  I have now included a sheet with all the calculations on, on the latest link

3. The date columns in the original table represent the expiration dates of the batches. They can only be used up to the point where they have 6 months remaing shelf life.

Many Thanks

https://www.dropbox.com/s/tz7pptkuis8bcdu/Inv_risk_demo_help_latest.xlsx?dl=0