Raw materials and their product prices over the time

[ahadtk]

Dear All!

We have an Excel table like below:

	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price
	01-01-2022	02-01-2022	03-01-2022	04-01-2022	05-01-2022	06-01-2022	07-01-2022	08-01-2022	09-01-2022	10-01-2022	11-01-2022	12-01-2022	13-01-2022	14-01-2022	15-01-2022
Raw Material 1	5.64	4.22	2.25	9.8	8.18	4.54	5.86	7.2	2.63	6.65	1.06	4.49	2.22	5.05	7.54
Raw Material 2	3.59	6.82	7.61	2.06	6.62	4.35	3.04	2.78	7.63	6.31	1.51	6.05	7.27	2.97	3.61
Raw Material 3	5.95	4.02	5.93	1.33	5.13	1.17	6.4	1.04	9.94	7.87	3.91	1.82	4.15	9.51	5.1
Raw Material 4	4.9	1.58	5.23	1.04	4.89	3.26	9.76	4.71	7.74	5.1	4.36	1.2	2.33	3.54	9.26
Raw Material 5	8.65	1.55	8.71	7.39	5.85	8.32	6.69	8.52	5.77	8.43	8.86	3.2	8.43	3.15	3.87
Raw Material 6	8.71	8.2	3.16	4.79	8.02	2.47	1.88	3.41	9.65	2.53	3.22	7.6	3.2	2.2	4.56
Raw Material 7	6.89	2.2	4.86	7.1	6.19	6.84	9.91	5.29	6.86	3.27	2.18	8.67	4.3	1.05	2.64
Raw Material 8	3.72	8.78	8.35	1.2	6.43	8.42	4.11	6.5	1.35	8.04	3.41	1.56	2.32	8.38	5.03
Raw Material 9	5.07	4.01	6.34	8.7	2.36	5.83	7.63	3.97	5.11	4.98	5.71	1.39	9.31	6.57	3.72
Raw Material 10	7.04	4.26	9.55	4.64	4.16	8.13	5.09	4.3	2.72	8.13	9.66	6	3.35	7.35	4.53
Raw Material 11	5.98	5.52	8.2	1.51	3.23	4.85	1.51	7.28	8.74	3.51	6.96	9.5	7.3	2.98	3.37
Raw Material 12	3.45	8.71	6.49	3.73	3.29	1.42	1.23	8.36	2.92	7.79	1.99	1.23	1.03	2.44	8.8
Raw Material 13	2.76	7.22	6.93	9.89	7.06	9.16	5.8	1.49	2.96	5.49	5.47	8.36	7.17	1.15	5.21
Raw Material 14	8.68	5.38	7.18	8.14	6.11	9.85	1.52	1.5	1.98	7.34	7.91	3.85	2.67	4.04	2.9
Raw Material 15	9	8.89	4.4	4.39	3.12	8.04	3.26	9.54	8.35	3.83	5.69	1.64	2.7	5.95	4.96
Raw Material 16	6	6.29	2.25	1.75	6.69	9.08	8.87	1.23	8.77	9.73	8.31	4.8	7.85	1.52	6.49
Raw Material 17	1.73	5.02	9.78	4.67	9.81	3.38	5.74	7.35	8.53	4.12	3.26	3.32	9.23	5.24	6.69
Raw Material 18	8.05	3.2	7.24	7.61	4.96	9.41	9.95	4.35	5.66	7.83	5.13	3.11	1.86	3.28	8.46
Raw Material 19	9.43	2.12	1.88	9.71	6.77	7.58	3.97	3.45	8.36	2.58	2.81	2.49	6.51	3.54	1.54
Raw Material 20	9.01	3.13	5.46	7.02	1.56	6.05	3.77	5.63	5.02	1.2	1.54	4.6	7.01	5.65	4.43

And another excel table like below:

Product 1

=

Raw Material 18

+

Raw Material 19

+

Raw Material 15

Product 2

=

Raw Material 17

+

Raw Material 5

+

Raw Material 1

+

Raw Material 16

Product 3

=

Raw Material 8

+

Raw Material 2

+

Raw Material 16

+

Raw Material 17

+

Raw Material 19

Product 4

=

Raw Material 8

+

Raw Material 15

+

Raw Material 9

+

Raw Material 17

+

Raw Material 14

+

Raw Material 2

Product 5

=

Raw Material 2

+

Raw Material 14

+

Raw Material 8

Product 6

=

Raw Material 15

+

Raw Material 19

+

Raw Material 18

+

Raw Material 16

+

Raw Material 3

Product 7

=

Raw Material 16

+

Raw Material 11

+

Raw Material 9

+

Raw Material 5

Product 8

=

Raw Material 6

+

Raw Material 7

Product 9

=

Raw Material 11

+

Raw Material 13

+

Raw Material 7

+

Raw Material 14

Product 10

=

Raw Material 20

+

Raw Material 5

+

Raw Material 1

We need to track the prices over time for raw materials and also the product,

I know that it is not hard to track the raw materials over time,

But, since we have thousands of products and thousands of raw materials and hundreds of prices back in time, it is hard to:

combine the prices of raw materials for a specific product and track its price over the time,

It would be great if you help me,

Thanks,

[ahadtk]

Hi @v-cgao-msft 

Thanks a lot 🙂

[v-cgao-msft · Accepted solution]

Hi @ahadtk ,

You may consider transposing the tables in the Power Query Editor, like:

Table1:

Table2:

Then create a new measure:

Measure = 
VAR _PRODUCT = MAX('Table 2'[Product])
VAR _DATE = MAX('Table 1'[Date])
VAR _MATERIALS = CALCULATETABLE(VALUES('Table 2'[Raw materials]),FILTER(ALL('Table 2'),'Table 2'[Product]=_PRODUCT))
VAR _PRICE = CALCULATE(SUM('Table 1'[Price]),'Table 1'[Date]=_DATE&&'Table 1'[Raw materials] IN _MATERIALS)
RETURN
_PRICE

Result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data