Forum Discussion
Calculated column with sum and filters
Hello
I don't manage to define the formula to crete the following calculated column :
I have 3 tables
Invoice
| InvoiceNum | Amount | Invoice Date |
| 123 | 34 | 01/01/2023 |
| 124 | 50 | 05/01/2023 |
Incomes
| IncomeNum | InvoiceNum | Amount | Date |
| 0001 | 123 | 20 | 05/01/2023 |
| 0002 | 123 | 10 | 01/02/2023 |
| 0003 | 124 | 40 | 25/01/2023 |
Events
| EventID | InvoiceNum | Date |
| 1 | 123 | 30/01/2023 |
| 2 | 124 | 01/02/2023 |
I try to add a column in the Events table to store the sum of incomes between the invoice date and the event date. I presume I need to use Calculate adn Filter function, but I don't manage to get something consistent.
Any help will be very appreciated
David
Hi dgranddemars ,
Here are the steps you can follow：
1. Create calculated column.
Date_Invoice = MAXX( FILTER(ALL(Invoice), 'Invoice'[InvoiceNum]=EARLIER('Events'[InvoiceNum])),'Invoice'[Invoice Date])Sum_Incomes = SUMX( FILTER(ALL(Incomes), 'Incomes'[Date]>=EARLIER('Events'[Date_Invoice])&&'Incomes'[Date]<=EARLIER('Events'[Date]) &&'Incomes'[InvoiceNum]=EARLIER('Events'[InvoiceNum])),[Amount])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Hi dgranddemars ,
Here are the steps you can follow：
1. Create calculated column.
Date_Invoice = MAXX( FILTER(ALL(Invoice), 'Invoice'[InvoiceNum]=EARLIER('Events'[InvoiceNum])),'Invoice'[Invoice Date])Sum_Incomes = SUMX( FILTER(ALL(Incomes), 'Incomes'[Date]>=EARLIER('Events'[Date_Invoice])&&'Incomes'[Date]<=EARLIER('Events'[Date]) &&'Incomes'[InvoiceNum]=EARLIER('Events'[InvoiceNum])),[Amount])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- Ashish_Mathur
Super User
Hi,
Show the expected result very clearly.
Thanks for your help 🙂
