Add column that summarizes a column from another table

[AFra]

Hi, 

I have two tables : 

A. 

Society	Target
A	100
B	200
C	1000
D	1200

B. 

Date	Society	Amount
29/02/2020	A	10
31/01/2020	B	12
29/02/2020	C	20
31/01/2020	D	10
29/02/2020	A	5
31/01/2020	B	10
29/02/2020	C	8
31/01/2020	D	12

I would like to add a new column on table A that summarizes the amounts (by society), but only for 29/02/2020. Thus, it would look like this : 

A. 

Society	Target	Amount
A	100	15
B	200
C	1000	28
D	1200

I took a few months off power bi and lost all my basics... 

Can someone help me with this? 

THanks in advance! 

ANa 

[v-shex-msft]

HI @AFra,

Did this date filter dynamic base on your filter/slicer? If this is a case, I'd like to suggest you write a measure formula to calculate. (current calculated column/table not able to interact with filter/slicer dynamically)

Measure =
CALCULATE (
    SUM ( TableB[Amount] ),
    FILTER ( ALLSELECTED ( TableB ), [Society] IN VALUES ( TableA[Society] ) ),
    VALUES ( TableB[Date] )
)

Regards,

Xiaoxin Sheng

[camargos88 · Accepted solution]

Hi @AFra ,

You can try this code for calculated column:

CALCULATE(SUM('Table'[Amount]); FILTER(ALL('Table'); 'Table'[Society] = EARLIER('Table (2)'[Society]) && 'Table'[Date] = DATE(2020; 2; 29)))

If you need it for measures, it will change a little bit.

Ricardo

[AFra]

It works nicely, thanks a lot!