Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum data after matching dates from 2 tables

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!

 

Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Hi Anonymous,

     

    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
        )
)

5 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Hi Anonymous,

     

    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
        )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        LivioLanzo
        Solution Sage
        7 years ago

        Hi Anonymous

         

        can you post a screenshot of what is happening?