Forum Discussion
Conditional Measure for specific row across two tables
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?
Since there is an additional complexity (as mentioned by you in another post), share a representative dataset and show the expected result.
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.
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.