Sum data after matching dates from 2 tables

[SheaBear]

Hi Everyone

I have one that stumped me. 

I want to sum $ values from one table based one dates from the this table and another table. 

Example:

contact_table

CID    failed_payment_date

123    2018-12-01

payments_table

CID    payment_date    payment_amount

123    2018-09-01        100

123    2018-10-01        100

123    2018-11-01        100

123    2019-01-01        100

123    2019-02-01        100

I want to sum all payment amounts after the failed payment date ie. the sum would be 200

The tables are joined on the CID. 

essentially im looking for

IF

'TB.B' [payment_date ] >= 'TB.A'[failed_payment_date] THEN SUM(payment_amount)

How do I accomplish this? new columns? USERRELATIONSHIP in a formula, etc?

Also to clear... Im fairly new to PBI and the first post in Community, I did dig through past posts and didnt really find what i was looking for, any help is much appreciated. Thanks!

[LivioLanzo · Accepted solution]

Hi @SheaBear,

create a 1-to-many relationship between concact_table and payment_table and then use this formula

=
SUMX (
    contact_table,
    VAR FailDate = contact_table[failed_payment_date]
    RETURN
        CALCULATE (
            SUM ( payments_table[payment_amount] ),
            payments_table[payment_date] > FailDate
        )
)

[SheaBear]

Hey @LivioLanzo - Thank you sir. Yes with some additional pieces that did the trick. 

Saved Amounts Test = SUMX (

'ContactTable',

VAR FailDate = 'IFS 1'[Saved]

RETURN

CALCULATE (

SUM ('Payments Table'[payment_amount]),

'Payments Table'[date] > FailDate,

FILTER('IFS 1','IFS 1'[Saved] <> BLANK()),

FILTER('Payments Table','Payments Table'[date] <> BLANK()),

FILTER('Payments Table','Payments Table'[payment_type] = "xxxx"

)

))

Just another follow up from this solution. (which it is the solution, thanks again) - I know without seeing the full file this may be a difficult one to answer, but!, when I try to roll them up by the Rows from one table or the other, while the individual values (when split by CID) are 100% correct, in the total it shoots up to the billions, meaning its creating the answer on every row of one of the tables (payments_table as there are individual lines for each and every payment). What ideas or advice would you give for making sure that that the total is not pulling from one table and only based on the single line table, meaning the (1) in the 1:* realationship?

Cheers!

[LivioLanzo]

Hi @SheaBear

can you post a screenshot of what is happening?

[SheaBear]

Hi @LivioLanzo

Sure

What im seeing is when drill down is on with a split by custom ID you can see it shows the right total by "cohort" meaning month, but behind the scenes its still totaling out to be sky high. 

For Context the below is only showing 1 cohort which should show that 20k as the total in the grand total at the bottom, but it shoots to 126 million. 

[LivioLanzo]

hI @SheaBear

what is difference between the contact table and IFS 1 table?

in my measure VAR FailDate is coming from the contact table, but i see it is not the case in your measure