Dynamic Unit Price DAX Measure

[radonwilson]

New Rate.xlsx
	A	B	C	D	E	F	G
1	Sales Table
2	Date	OrderID	SKU	Qty	Amount	Amount Breakup	SKU in Stocks
3	01-01-2024	1	BT-01	2	200	2 X 100 = 200	2 X 100 + 6 X 120 = 920
4	01-01-2024	2	BT-01	3	320	2 X 100 + 1 X 120 = 320	0 X 100 + 5 X 120 = 600
5	02-01-2024	3	BT-03	2	660	2 X 330 = 660	3 X 330 + 6 X 300 = 2790
6	02-01-2024	4	BT-03	4	1290	3 X 330 + 1 X 300 = 1290	0 X 330 + 5 X 300 = 1500
7	03-01-2024	5	BT-02	3	600	3 X 200 = 600	1 X 200 + 6 X 230 = 1580
8	03-01-2024	6	BT-02	2	430	1 X 200 + 1 X 230 = 430	0 X 200 + 5 X 230 = 1150
9	04-01-2024	7	BT-01	2	240	3 X 120 = 360	0 X 100 + 2 X 120 = 240
10	04-01-2024	8	BT-01	1	120	1 X 120 = 120	0 X 100 + 1 X 120 = 120
11	05-01-2024	9	BT-03	3	900	3 X 300 = 900	0 X 330 + 2 X 300 = 600
12	05-01-2024	10	BT-02	3	690	3 X 230 = 690	0 X 200 + 2 X 230 = 460
Sheet1

Cell Formulas
Range	Formula
E4	E4	=200+120
E5	E5	=330*2
E6	E6	=330*3+300
E7	E7	=200*3
E8	E8	=200+230
E11	E11	=300*3
E12	E12	=230*3

New Rate.xlsx
	I	J	K	L
1	Purchase Table
2	SKU	Date	Qty	Rate
3	BT-01	01-12-2023	4	100
4	BT-01	05-12-2023	6	120
5	BT-02	02-12-2023	4	200
6	BT-02	06-12-2023	6	230
7	BT-03	04-12-2023	5	330
8	BT-03	09-12-2023	6	300
9	BT-01	12-12-2023	10	90
Sheet1

New Rate.xlsx
	I	J	K
11	Current Stocks
12	SKU	Qty	Rate
13	BT-01	1	120
14	BT-02	2	230
15	BT-03	2	300
16	BT-01	10	90
Sheet1

1. I want to calculate the Amount dax measure as shown in Sales[Amount] col.
2. For the earliest transaction in the Sales Table, I want to look for the earliest SKU rate from the Purchase Table to calculate the amount.
3. There could be a situation where a multi-qty transaction of the same SKU would have different rates.
for example → Have a look at 2nd transaction. This transaction has 3 units and the amount is calculated as, for the first 2 units the rate is 100 and for the 3rd unit the rate is 120.
So the amount would be 2 X 100 + 1 X 120 = 320