Consuming forecast

[SJ]

Guys,

Help I am struggling!

I am trying to create a calculation to show how big a risk we are facing from stock going out of date.

I have 2 datasets 1 contains our forecast:

Secondly I have the batch data to show our current stock holding and sell by date.

The data in this example assumes that today is the 06-Auguset.

I need to see how much each line will consume of the forecast and thus how much is left at risk.

For illustration purposes I have created this:

Can any of you think of a way either in M query or Dax that can calculate the level of risk we are facing by batch.

I am showing just 1 material code above but we have many.

I can send over a spreadsheet with the above if need be.

Your help is much appreciated!

[v-yuta-msft]

Hi SJ,

You should merge Table1 of dataset1 and Table2 of dataset2 based on the material column and then create a calculate column of measure to implement your forecast logic. In addtion, could you clarify more details about the logic of your risk forecast? Could you share some formula you are using currently for further analysis?

Regards,

Jimmy Tao