Forum Discussion
Conditional Measure for specific row across two tables
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 mat...
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.