Overlap calculation

[William_Moreno]

Hi everyone, Today I need your help!

I have this problem:

Demand forecast like below:

SKU	DESCRIPTION	PRODUCTION PLAN	UM	MONTH
200	CAKE	10	tons	mar/21
200	CAKE	20	tons	abr/21
200	CAKE	30	tons	mai/21

Two Bill Of Material

First level

SKU	DESCRIPTION		COMPONENTS	COMPONENTS DESCRIPTION	QTY PER tons	UM
200	CAKE		600339	PACK	0,5	KG
200	CAKE		600340	BOX	2	UNIT
200	CAKE		600341	ETIQ	1	UNIT
200	CAKE		700100*	SEMI CAKE	1000	KG

Second level

SKU	DESCRIPTION	COMPONENTS	COMPONENTS DESCRIPTION	QTY PER BOM	UM
700100*	SEMI CAKE	500001	SUGAR	500	KG
700100*	SEMI CAKE	500002	FOSF	200	KG
700100*	SEMI CAKE	500001	MILK	300	KG

And I would wish this output:

COMPONENTS	COMPONENTS DESCRIPTION	UM	QTY	MONTH
600339	PACK	KG	5	mar/21
600340	BOX	UNIT	20	mar/21
600341	ETIQ	UNIT	10	mar/21
700100	SEMI CAKE	KG	10000	mar/21
500001	SUGAR	KG	5000	mar/21
500002	FOSF	KG	2000	mar/21
500001	MILK	KG	3000	mar/21
600339	PACK	KG	10	abr/21
600340	BOX	UNIT	40	abr/21
600341	ETIQ	UNIT	20	abr/21
700100	SEMI CAKE	KG	20000	abr/21
500001	SUGAR	KG	10000	abr/21
500002	FOSF	KG	4000	abr/21
500001	MILK	KG	6000	abr/21
600339	PACK	KG	15	mai/21
600340	BOX	UNIT	60	mai/21
600341	ETIQ	UNIT	30	mai/21
700100	SEMI CAKE	KG	30000	mai/21
500001	SUGAR	KG	15000	mai/21
500002	FOSF	KG	6000	mai/21
500001	MILK	KG	9000	mai/21

Is it possible by measures?

Thank you in advanced

[William_Moreno]

@ImkeF 
Thank you so much for answer me, it really help me a lot. 

[ImkeF · Accepted solution]

Hi @William_Moreno ,
looks like you need 2 merges for it.

let
    Source = DemandForecast,
    #"Merged Queries" = Table.NestedJoin(Source, {"DESCRIPTION", "SKU"}, FirstLevel, {"DESCRIPTION", "SKU"}, "FirstLevel", JoinKind.LeftOuter),
    #"Expanded FirstLevel" = Table.ExpandTableColumn(#"Merged Queries", "FirstLevel", {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER tons", "UM"}, {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER tons", "UM.1"}),
    #"Inserted Multiplication" = Table.AddColumn(#"Expanded FirstLevel", "Multiplication", each [QTY PER tons] * [PRODUCTION PLAN], Int64.Type),
    Level1 = Table.RenameColumns(#"Inserted Multiplication",{{"Multiplication", "Amount"}}),
    #"Merged Queries1" = Table.NestedJoin(Level1, {"COMPONENTS"}, SecondLevel, {"SKU"}, "SecondLevel", JoinKind.Inner),
    #"Removed Other Columns" = Table.SelectColumns(#"Merged Queries1",{"PRODUCTION PLAN", "MONTH", "SecondLevel"}),
    #"Expanded SecondLevel" = Table.ExpandTableColumn(#"Removed Other Columns", "SecondLevel", {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER BOM", "UM"}, {"COMPONENTS", "COMPONENTS DESCRIPTION", "QTY PER BOM", "UM.1"}),
    Level2 = Table.AddColumn(#"Expanded SecondLevel", "Amount", each [PRODUCTION PLAN] * [QTY PER BOM], Int64.Type),
    Custom1 = Level1 & Level2,
    #"Removed Other Columns1" = Table.SelectColumns(Custom1,{"COMPONENTS", "COMPONENTS DESCRIPTION", "UM.1", "Amount", "MONTH"})
in
    #"Removed Other Columns1"

Please check enclosed file.