Forum Discussion
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...
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 ) )
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
Solution Sage
Solution SageHi Anonymous
can you post a screenshot of what is happening?