Calculating two columsn in seperate tables with a dependency on another column

[Mantrahalos]

Hi Mr Excel - I hope someone can help. The data below shows two tables I have in powerbi. (X and Y)

I am trying to create a column in a table (column J in the data below) - I am trying to calculate a total sales for a particular week - The cost of each item changes from week to week.
So - If in week 1 we sell 3 of widget 3 then i want to calculate 9 times the cost of widget 3 (at the week 1 price) - I have a seperate table (Table X in data below) that shows the cost of each item by each week. I cant see a way to add a relationship as neither columns have unique values.

Can anyone assist? Have I given enough information for what I am trying to achieve?

Thanks.

Mantrahalos

A	B	C	D	E	F	G		H	I	J	K
	Table X							Table Y
Week	Average cost table	Item			Week	date	Store code	sales quantity	Item	Expect to see	total sales this week
Week1	35​	Item3			Week1	01/01/2019​	1​	3​	Item3	105​	3*item3@week1price in tableX
week2	65​	Item3			week1	08/01/2019​	2​	4​	Item3	260​
week1	32​	item4			week3	15/01/2019​	3​	56​	item4	1064​
week4	19​	item3			week1	22/01/2019​	4​	3​	item3
week4	525​	item1			Week5	29/01/2019​	4​	4​	item1
Week6	52​	item1			Week6	05/02/2019​	1​	53​	item1
Week7	85​	item6			week4	12/02/2019​	2​	85​	item6
Week8	96​	item1			week4	19/02/2019​	3​	5​	item1
Week9	841​	Item3			Week9	26/02/2019​	4​	6​	Item3
Week10	32​	Item3			week9	05/03/2019​	3​	7​	Item3
Week11	23​	item4			Week11	12/03/2019​	2​	89​	item4
Week12	132​	item3			Week12	19/03/2019​	5​	976​	item3
Week13	32​	item1			Week13	26/03/2019​	4​	5​	item1
Week14	63​	item1			Week14	02/04/2019​	3​	8​	item1
Week15	32​	item6			Week15	09/04/2019​	5​	3​	item6
Week16	12​	item1			Week16	16/04/2019​	3​	55​	item1
Week17	3​	Item3			Week17	23/04/2019​	2​	6​	Item3
Week18	113​	Item3			Week18	30/04/2019​	3​	77​	Item3
Week19	21​	item4			Week19	07/05/2019​		84​	item4
Week20	21​	item3			Week20	14/05/2019​		46​	item3

 

[Toadstool]

Hi Mantrahalos,

I'm unsure why you would multiply by 9 if the sales are 3 so I've assumed you meant 3.
I suspect the second row of the Y table should be for week2 to provide the result 260, so I have changed it accordingly.
Table X would benefit from a year, or actual date, to support data which may overlap a year.
You also have some entries which have no matches for the specified week number so I have shown "No price found". In those cases if you actually want the item price from an earlier week to be used then I would employ a different formula.

Mantrahalos.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		Table X							Table Y
2	Week	Average cost table	Item			Week	date	Store code	sales quantity	Item	Expect to see
3	Week1	35	Item3			Week1	01-Jan-19	1	3	Item3	105
4	week2	65	Item3			Week2	08-Jan-19	2	4	Item3	260
5	week1	32	item4			week3	15-Jan-19	3	56	item4	No price found
6	week4	19	item3			week1	22-Jan-19	4	3	item3	105
7	week4	525	item1			Week5	29-Jan-19	4	4	item1	No price found
8	Week6	52	item1			Week6	05-Feb-19	1	53	item1	2756
9	Week7	85	item6			week4	12-Feb-19	2	85	item6	No price found
10	Week8	96	item1			week4	19-Feb-19	3	5	item1	2625
11	Week9	841	Item3			Week9	26-Feb-19	4	6	Item3	5046
12	Week10	32	Item3			week9	05-Mar-19	3	7	Item3	5887
13	Week11	23	item4			Week11	12-Mar-19	2	89	item4	2047
14	Week12	132	item3			Week12	19-Mar-19	5	976	item3	128832
15	Week13	32	item1			Week13	26-Mar-19	4	5	item1	160
16	Week14	63	item1			Week14	02-Apr-19	3	8	item1	504
17	Week15	32	item6			Week15	09-Apr-19	5	3	item6	96
18	Week16	12	item1			Week16	16-Apr-19	3	55	item1	660
19	Week17	3	Item3			Week17	23-Apr-19	2	6	Item3	18
20	Week18	113	Item3			Week18	30-Apr-19	3	77	Item3	8701
21	Week19	21	item4			Week19	07-May-19		84	item4	1764
22	Week20	21	item3			Week20	14-May-19		46	item3	966
Sheet1

Cell Formulas
Range	Formula
K3:K22	K3	=IFERROR(INDEX($B$3:$B$9999,MATCH(1,INDEX(($A$3:$A$9999=F3)*($C$3:$C$9999=J3),0,1),0))*I3,"No price found")

 

[Mantrahalos]

Hi Toadstool - Thank you so much for taking the time to reply - You were right - i did make some basic errors in my post - which you spotted and put right. I am sorry to say that my plans for the way this works changed after some further thought - so i didn't use this in the end - but just wanted to type a quick note of appreciation.

Mantrahalos

 

[Toadstool]

Thanks for letting me know.

I started programming almost 50 years ago so I'm pretty sure 99%+ of anything I've written is now obsolete... although probably not as quickly as this