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. 

       

      • LivioLanzo's avatar
        LivioLanzo
        Icon for Solution Sage rankSolution Sage
        7 years ago

        hI Anonymous

         

        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