Add according to condition

[Syndicate_Admin]

Dear friends, I need your help to solve a problem that has arisen and I can't find a way to solve it

I have 2 tables which are related from 1 (VW_EvaProData2(2) to many (VW_EvaProRec). In the first table I have a list that corresponds to the purchase orders executed with all the information related to them, and in the second table I have the information to the receipts of the products associated with the purchase orders.

What I need to do is to be able to make a column which indicates the sum of what I received before a certain date

NumOC	FechaOC	Fecha_Comp	state	CodAux	ObsOC	Total$
102630	07/01/2022	14/01/2022	Approved	9205200060	STOCK	5415028

CodProd	Fecha_R	Order	CantIngresada	ocCorrela	PreUniMB	Valor_recep	Fech_comp	State
H25101851	13/01/2022	102630	4	1	13829	55316	14/01/2022	Delayed
H45100100	13/01/2022	102630	10	2	3431	34310	14/01/2022	Delayed
K20101900	13/01/2022	102630	100	3	29828	2982800	14/01/2022	Delayed
K20150300	13/01/2022	102630	10	4	19312	193120	14/01/2022	Delayed
R45100300	09/02/2022	102630	100	5	6487	648700	14/01/2022	In PLazo
R45100303	25/01/2022	102630	73	6	4284	312732	14/01/2022	In PLazo
R45100303	09/02/2022	102630	27	6	4284	115668	14/01/2022	In PLazo
R45100400	25/01/2022	102630	56	7	8427	471912	14/01/2022	In PLazo
R45100400	13/01/2022	102630	24	7	8427	202248	14/01/2022	Delayed
R45100500	25/01/2022	102630	10	8	12076	120760	14/01/2022	In PLazo
G18100200	13/01/2022	102630	100	9	1628	162800	14/01/2022	Delayed
G18100223	09/02/2022	102630	50	10	2297	114850	14/01/2022	In PLazo

The idea is to be able to add a column in the first table (Summary Table) that tells me the sum of the value column Valor_recep, when the date field is Fecha_R less than or equal to Fecha_Comp. In the example, it should look like

[v-tangjie-msft · Accepted solution]

Hi @Syndicate_Admin ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a calculated column.

Column = CALCULATE(SUM('Table (2)'[Valor_recep]),FILTER('Table (2)','Table (2)'[Order]=EARLIER('Table'[NumOC]) && 'Table (2)'[Fecha_R] <='Table (2)'[Fech_comp]))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.