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_da...
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
        )
)
LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution 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
      Icon for Solution Sage rankSolution Sage
      7 years ago

      Hi Anonymous

       

      can you post a screenshot of what is happening?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.