Hello, I have encountered a problem, trying to write a measure.
This measure should account for price per unit in Received and Warehouse.
Warehouse is simple because both tables Received and Issued has Warehouse. Price per unit is different, the issued item has no price per unit, so the logic should be that the first items that arrive in the warehause are the first items issued. For example: I have received 5 item a worth 0.5 per unit and 6 item a worth 0.6 per unit, then issue 7 items, so the first ones to go are items worth 0.5 per unit, and because there are 2 more items to issue i have to take them from the newer batch that cost 0.6 per unit. So after all calculations i should be left with 4 items worth at 0.6 per unit.
An example with dummby data:
I have 3 tables in power bi:
1st table is Received
| Date | Item | Warehause | PricePerUnit | QTY(Received) |
| 2024-05-15 | 12a | gs1 | 3.5 | 3 |
| 2024-05-17 | 12a | gs1 | 3.8 | 7 |
| 2024-06-01 | 12a | gs2 | 4.5 | 9 |
| 2024-06-05 | 12a | gs1 | 3.5 | 3 |
2nd table is Issued:
| Date | Item | Warehause | QTY(Issued) |
| 2024-05-18 | 12a | gs1 | 5 |
| 2024-06-03 | 12a | gs2 | 8 |
| 2024-06-10 | 12a | gs1 | 6 |
3rd table is a simple DateTable with Date
So if i would select that i want to see Remaining quantities from 2024-05-19 i would get:
| Item | Warehause | PricePerUnit | RemainingQTY(New Measure) |
| 12a | gs1 | 3.5 | 0 |
| 12a | gs1 | 3.8 | 5 |
And if I would want to see Remaining quantities from 2024-06-18 i would get:
| Item | Warehause | PricePerUnit | RemainingQTY(New Measure) |
| 12a | gs1 | 3.5 | 2 |
| 12a | gs1 | 3.8 | 0 |
| 12a | gs2 | 4.5 | 1 |
Any help with this measure would be highly appreciated.
