Calculated column with sum and filters

[dgranddemars]

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

[dgranddemars]

Thanks for your help 🙂

[Ashish_Mathur]

Hi,

Show the expected result very clearly.

[v-yangliu-msft · Accepted solution]

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