Production material cost based on prices and partslist changing over time

[tdobai]

Hi Community, I'm trying to calculate total material costs of produced SKU's as a multiplication of partslist items * items' purchase prices (partslist aka BOM ~ bill of material). The challenge is that both partslist items and their purchase prices change over time constantly, therefore both stored with from-to validity date ranges.

Changes in BOMs and prices accour on different days, they typically do not coincide. The result (total material cost of produced SKU's) should be available over time, at least in those from-to date ranges whenever total material cost change for any reason. The BOMs and prices are stored in two different tables, I pasted part of them here for samples, hope the'll display fine.

Raw material historical prices:

MatID	UoM	Price	From	To
Raw material1	kg	1,5	2023.01.01	2023.09.30
Raw material1	kg	0,83	2023.10.01	9999.12.31
Raw material2	kg	1,2	2023.10.01	9999.12.31
Raw material3	batch	2	2023.01.01	2023.06.15
Raw material3	batch	1,92	2023.06.16	9999.12.31
Raw material4	ea	3	2023.01.01	9999.12.31
Raw material5	kg	4	2023.01.01	9999.12.31
Raw material6	batch	5	2023.01.01	2023.06.30
Raw material6	batch	5,21	2023.07.01	9999.12.31
Raw material7	ea	6	2023.01.01	9999.12.31
Raw material8	ea	5	2023.01.01	9999.12.31
Raw material9	kg	7	2023.01.01	2023.06.30
Raw material9	kg	7,5	2023.07.01	9999.12.31
Raw material10	ea	8	2023.01.01	9999.12.31
Raw material11	kg	9	2023.01.01	2023.09.30
Raw material11	kg	9,35	2023.10.01	9999.12.31

Historical BOMs:

SKUNr	MatID	UOM	Qty	From	To
10001	Raw material1	kg	1	2023.01.01	2023.12.31
10001	Raw material1	kg	1,2	2024.01.01	9999.12.31
10001	Raw material2	batch	2	2023.01.01	9999.12.31
10001	Raw material3	ea	3	2023.01.01	9999.12.31
10001	Raw material4	kg	4	2023.01.01	9999.12.31
10001	Raw material5	batch	5	2023.01.01	9999.12.31
10001	Raw material6	ea	6	2023.01.01	2023.07.31
10001	Raw material6	ea	5	2023.08.01	9999.12.31
10001	Raw material7	kg	7	2023.01.01	9999.12.31
10001	Raw material8	ea	8	2023.01.01	9999.12.31
10001	Raw material9	kg	9	2023.01.01	9999.12.31
10001	Raw material10	batch	10	2023.01.01	9999.12.31
10002	Raw material2	batch	1	2023.01.01	9999.12.31
10002	Raw material3	ea	2	2023.01.01	2023.08.31
10002	Raw material3	ea	1,7	2023.09.01	9999.12.31
10002	Raw material4	kg	3	2023.01.01	9999.12.31
10002	Raw material5	batch	4	2023.01.01	9999.12.31
10002	Raw material6	ea	5	2023.01.01	2023.06.30
10002	Raw material6	ea	4,5	2023.07.01	9999.12.31
10002	Raw material7	kg	6	2023.01.01	9999.12.31
10002	Raw material8	ea	7	2023.01.01	9999.12.31
10002	Raw material9	kg	8	2023.01.01	2024.01.01
10002	Raw material9	kg	7,1	2024.01.02	9999.12.31

Can anyone please advise how to solve this in DAX?

Appreciate any help.

[danextian]

hi @tdobai ,

Where can I f ind these in your sample data?

• partslist items
• items' purchase prices

Can you also provide expected result and expound on your logic? For example,  for row 1 the result is this and that and why.

[dobait]

Hello @danextian,

Thank you for the swift reply. 

• Partslist items are referred to as Raw material1, Raw material2,... etc.
• Items' purchase prices are referred to as 'Price'.

To make it clear, I pasted below a sample report showing what I expect as outcome. Included are both built in quanitites ('qty' column) as well as prices ('mat.price' column), all with validity dates ('valid from' column). Note, 'mat.cost' = 'qty' * 'mat.price'.

Basically, I need the history of total material cost of an SKU (parent) item, see "Mat.cost' in bottom line of below report for SKU '10001'. All price/quantity changes highlighted in orange for ease of use.

[danextian · Accepted solution]

hI @dobait ,

What you are trying to achieve while not impossible requires a complex logic - eg Row 2 in your table doesn't exist in your sample data so it must exist first before proceeding with other calculations - and quite some time as well to formulate. I have attached a sample pbix for you to get started with.