sum distinct values of the max date

[JMAROUF]

Hi,

I'm looking to sum distinct values of the max date before the selected one (saved in v_StartDate), here is the input:

DATE	INVESTEE	INVESTOR	PART	VALUE	TRANSCTION
01/01/2021	INVESTEE_1	INVESTOR_1	A	100	1
01/01/2021	INVESTEE_1	INVESTOR_1	B	200	2
01/02/2021	INVESTEE_1	INVESTOR_1	A	100	3
01/02/2021	INVESTEE_1	INVESTOR_1	B	200	4
01/10/2021	INVESTEE_2	INVESTOR_1	A	50	5
02/10/2021	INVESTEE_2	INVESTOR_1	B	150	6
31/11/2021	INVESTEE_2	INVESTOR_1	E	50	7
31/11/2021	INVESTEE_2	INVESTOR_1	F	150	8
01/12/2021	INVESTEE_3	INVESTOR_1	C	300	9
01/12/2021	INVESTEE_3	INVESTOR_1	D	400	10
03/12/2021	INVESTEE_3	INVESTOR_1	C	300	11
03/12/2021	INVESTEE_3	INVESTOR_1	D	400	12
01/12/2021	INVESTEE_4	INVESTOR_1	C	350	13
01/12/2021	INVESTEE_4	INVESTOR_1	D	450	14
31/12/2021	INVESTEE_4	INVESTOR_1	C	350	15
31/12/2021	INVESTEE_4	INVESTOR_1	D	450	16

Output should be like this:

v_StartDate is 31/12/2021, so I'm looking to sum distinct values for each INVESTEE/INVESTOR/PART for the last lower or equal to the selected one, here is my formula:

sum(aggr(sum({<[Date]={'=MAX({<[Date]={"<=$(=v_StartDate)"}>} [Date])'}>}
DISTINCT VALUE),INVESTEE,INVESTOR,PART))

this sums all distinct values before the selected date, any help please?

[JMAROUF · Accepted solution]

I have flaged max dates for each distinct combinaison, and used this formula

=sum( aggr(sum({<Flag_Max_D_Trans ={1}>} DISTINCT VALUE),INVESTEE,INVESTOR,PART))