Conditional Measure for specific row across two tables

[Anonymous]

Dear all,

I am looking to include a measure in my report which calculates the difference between inventory and open orders. The values come from different source tables which are connected by material as common value. The outcome of the measure should show the inventory balance for the specific material that is on the order showing in the corresponding row of the report. 

My report looks like this: 

In a new column, I want to add the measure showing inventory balance, so for the first row with order 1, it should show 10 as inventory balance (100 inventory minus open orders of 10 , 10 , 20 and 50). All measures with substraction that I tried give me results that are not corresponding to the single material on the row. Which measure should I use for inventory balance to reflect the correct value for this material specifically on the correct row in the report? 

Thanks in advance for your help.

Below the source tables that I use, which have a relationship for Material_Plant.

Order Number	Material_Plant	Net_weight
1	001_X	10
2	002_X	20
3	003_X	10
4	001_X	10
5	001_X	20
6	001_X	50
7	003_X	10
8	001_Y	20
9	001_Y	10
10	002_Y	20
11	004_Y	10
12	004_Y	50
13	005_Y	30

Material_Plant	Inventory
001_X	100
002_X	100
004_Y	100
005_X	100
005_Y	100

[Ashish_Mathur]

Hi,

So in that new column (result of a measure), you want to show 10 in each row where 001_X is found?  Am i correct?

[Anonymous]

Hi Ashish, Yes that is correct, the outcome would need to be shown in every row.

[Ashish_Mathur]

Since there is an additional complexity (as mentioned by you in another post), share a representative dataset and show the expected result.

[Anonymous]

Adding the loading date, the order table would look like this: 

Order Number	Loading Date	Material_Plant	Net_weight
1	25/12/2023	001_X	10
2	27/12/2023	002_X	20
3	27/12/2023	003_X	10
4	05/12/2023	001_X	10
5	08/12/2023	001_X	20
6	08/12/2023	001_X	50
7	16/12/2023	003_X	10
8	31/12/2023	001_Y	20
9	06/12/2023	001_Y	10
10	08/12/2023	002_Y	20
11	25/12/2023	004_Y	10
12	26/12/2023	004_Y	50
13	03/12/2023	005_Y	30

And the report would look like this: 

I would be happy with having the calculated remaining inventory measure to reflect the inventory minus all summarized order weight on each row, like this: 

Order Number	Loading Date	Material_Plant	Net_weight	Proj Inventory 1
1	25/12/2023	001_X	10	10
2	27/12/2023	002_X	20	80
3	27/12/2023	003_X	10	-20
4	05/12/2023	001_X	10	10
5	08/12/2023	001_X	20	10
6	08/12/2023	001_X	50	10
7	16/12/2023	003_X	10	-20
8	31/12/2023	001_Y	20	-30
9	06/12/2023	001_Y	10	-30
10	08/12/2023	002_Y	20	-20
11	25/12/2023	004_Y	10	40
12	26/12/2023	004_Y	50	40
13	03/12/2023	005_Y	30	70

Or even better, in the way that lbendlin has proposed with a decreasing projected inventory, but then based on loading date sequence rather than order number sequence.

Order Number	Loading Date	Material_Plant	Net_weight	Proj Inventory 2
1	25/12/2023	001_X	10	10
2	27/12/2023	002_X	20	80
3	27/12/2023	003_X	10	-20
4	05/12/2023	001_X	10	90
5	08/12/2023	001_X	20	70
6	08/12/2023	001_X	50	20
7	16/12/2023	003_X	10	-10
8	31/12/2023	001_Y	20	-30
9	06/12/2023	001_Y	10	-10
10	08/12/2023	002_Y	20	-20
11	25/12/2023	004_Y	10	90
12	26/12/2023	004_Y	50	40
13	03/12/2023	005_Y	30	70

Thanks again for your help.

[Ashish_Mathur]

Hi,

Try these calculated column formulas

Inventory = RELATED(Inventory[Inventory])

Cumulative net weight = CALCULATE(SUM(Data[Net_weight]),FILTER(Data,Data[Material_Plant]=EARLIER(Data[Material_Plant])&&Data[Loading Date]<=EARLIER(Data[Loading Date])&&Data[Order Number]<=EARLIER(Data[Order Number])))

Projected inventory = [Inventory]-[Cumulative net weight]

Hope this helps.

[lbendlin]

see attached

[Anonymous]

Thank you so much for this! An added complexity in my data set (I simplified it for the purpose of the post) is that every order also has a loading date, and orders with a higher order number could have an earlier loading date than an order with a lower order number. Is there a way to reflect loading date in your code, so that an order that loads earlier is consuming inventory first? I appreciate the help.