How to add calculated column in a main table that would calculate sum from another table in dax

Question

I have two table that are not related:

Table1:

Table2:

How can I simply add a calculated column that would grab a sum(TotalIncurred) per each PolicyNumber where DateCreated >= 10/19/2018

So desirable result should be:

Do I need to relate those two tables first?

Or there is a way to calculate it based on PolicyNumber without creating a relationship?

I tried to create a seperate table first, that would group all TotalIncurred and PolicyNumber:

LossesTableTest = 
ADDCOLUMNS(
    SUMMARIZE(
        FILTER(fact_Losses, fact_Losses[PolicyNumber] <> BLANK()),
              fact_Losses[PolicyNumber]
              ),
            "TotIncurred", CALCULATE(SUMX(fact_Losses, fact_Losses[TotalIncurred]))
)

And then add calculated column LossPerPolicy in a main table:

LossPerPolicy = 

    SUMX(
        FILTER(LossesTableTest, LossesTableTest[PolicyNumber] = fact_Premium[PolicyNumber] ), 
        LossesTableTest[TotIncurred]
        )

But the numbers are not correct.

Answer 1

Something like this should do the trick:

TotalIncurred =
CALCULATE (
    SUM ( 'Table2'[TotalIncurred] ),
    TREATAS ( VALUES ( 'Table1'[PolicyNumber] ), 'Table2'[PolicyNumber] ),
    FILTER ( 'Table2', 'Table2'[DateCreated] >= '10/19/2018' )
)

untested but seems like it will work :)

Hope it helps!

Answer 2

Give it a try using SUMX here, Although I have no way to test it :)

TotalIncurred =
SUMX (
    'Table1',
    CALCULATE (
        VALUES ( 'Table2'[TotalIncurred] ),
        FILTER (
            'Table2',
            'Table2'[DateCreated] >= '10/19/2018'
                && 'Table1'[PolicyNumber] = 'Table2'[PolicyNumber]
        )
    )
)

Answer 3

I prefer relating tables for this solution. If you have an unique policy number in table1, you should relate them to make this easier. If it's not unique, you can add a third table with unique policy numbers and relate them. The add a calculated column like this:

SUMX (    
    FILTER(
        RELATEDTABLE(Table2),
        'Table2'[DateCreated] >= '10/19/2018' 
    ), 
    'Table2'[TotalIncurred]
)