Inventory calculation. Covering the demand in regards to dates of the incoming batches

[Anonymous]

Hi everyone,

I have the task that I can't find a key to and I hope anyone here can help me.

This is about inventory calculations with incoming materials (production) and outcoming goods (sales).

I know this topic was discussed here, but several details make it hard for me to solve:

• Incoming materials are divided into batches.
• A final order is not total, it is split for different markets and different sizes.
• There is a need to know where the incoming batch will be used, for what size and what market.

Everything is converted and calculated in kg for simplicity.

Here is the data we have:

We have a schedule and weight of the incoming batches and a volume of an order for a month.

Using that I've created the running totals for production and consumption.

I need to get a system to predict what the incoming batch and for which final order will be used.

Order can be bigger than batch, and batch can be bigger than order.

Final orders have an index that is considered a priority and is used for creating a queue.

I would really appreciate any help or even direction with this.

I've done some movements by myself 

[Anonymous]

I don't know why a picture did not show up.

I uploaded it again.

[v-joesh-msft]

Hi  @Anonymous ,

I'm a little confused by your description. Sample data and expected output would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

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

[Anonymous]

Hi @v-joesh-msft ,

Thank you for your answer!

Here is the file with Data 

Here is how I see the final result 

The idea is to get as output the list of batches that will be used for the production of specific products.

[v-joesh-msft]

Hi  @Anonymous ,

I did some research, but still can't understand your intentions. Can you explain your calculation logic? It would be helpful if there is a logic example.

Best Regards,

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

[Anonymous]

Hi @v-joesh-msft ,

I am really grateful for your attention to this issue!

I am sorry for the trouble from my side in explaining it.

I made another table with the expected results in this file.

This Excel file has detailed formulas and shows my vision of the final result.

But I don't have a clue how to get that compressed table out of the initial one in PowerBI.

Here is a short description of the columns from the Final results table:

Date when available: A date when a batch with incoming materials can be transformed into Finished Good (packaged).

It is based on the date when incoming materials are quality released.

Therefore if the date when materials were quality released is before today, then "Date when available" = Today().

FG SKU # : Index number of Finished Goods. This number can be used for prioritizing, FG with a lower number is in higher priority than a bigger number.

Incoming batch used to cover demand: Index number of Incoming materials. Materials with a lower number shall be used first.

This month projected consumption, kg :

Incoming batch weight, kg: Total weight fo each batch of incoming materials.

Incoming materials USED, kg (calculated):  The volume of the incoming batch packaged for the specific order. 

Incoming materials LEFT, kg (calculated): The volume of the incoming batch is left. Cumulative total

Consumption COVERED, kg (calculated): Covered demand of each order by existing SKUs

Consumption is LEFT, kg (calculated): The uncovered demand of each order by existing SKUs. Cumulative Total

Thanks