Storing sum of items by week in a new table

[Dr_Chris]

Hi
I've searched the forum and tried to get this to work but still stumped.

I've got a table from production that records all the items booked into the warehouse.   There can be multiple entries for each product each day as they are simply scanned as they come into the warehouse.  The Production-Actual table contains many different field including the following ([Prod Week] is one that I have already created based on the date)

Production-Actual
Date	Product	Qty	Prod Week
01/11/2020	A	1	2020 - 44
01/11/2020	A	20	2020 - 44
02/11/2020	B	3	2020 - 45
03/11/2020	A	15	2020 - 45
03/11/2020	C	43	2020 - 45
05/11/2020	D	2	2020 - 45
06/11/2020	B	21	2020 - 45
06/11/2020	A	10	2020 - 45
06/11/2020	B	2	2020 - 45

For each prodcuct we have a second table which holds (along with other information) the weekly production targets for that product - for instance, for Item A

Target-Product A
Week	Target
2020 - 42	50
2020 - 43	50
2020 - 44	75
2020 - 45	90
2020 - 46	110
2020 - 47	110

I'd like to add a new column to "Target-Product A" which holds the total number of that product produced each week, so for Product A we would have

Target-Product A
Week	Target	Produced
2020 - 42	50	0
2020 - 43	50	0
2020 - 44	75	21
2020 - 45	90	25
2020 - 46	110	0
2020 - 47	110	0

Can anyone help me with the correct DAX expression for the [Produced] column please?

Thanks

[DataInsights · Accepted solution]

@Dr_Chris,

Try this calculated column in table Target-ProductA:

Produced = 
VAR vProduct = "A"
VAR vWeek = 'Target-ProductA'[Week]
VAR vProductionTable =
    FILTER (
        'Production-Actual',
        'Production-Actual'[Product] = vProduct
            && 'Production-Actual'[Prod Week] = vWeek
    )
VAR vResult =
    SUMX ( vProductionTable, 'Production-Actual'[Qty] )
RETURN
    vResult

[Dr_Chris]

Amazing - worked first time!

Thanks