Bring the first next date value from different table greater than current column date.

[Bigboss]

I want to bring purchase value from table sell to return table. 

I want to bring the first min value which is greater than Return date, in this case it is going to be 18-Aug-23. 

Thanks

Sell Table
Purchase Date	sor_rejection_crm_id	GROSS_SALES_NET_DISC_GROSS_RET
09-Jun-23	0046R43ZPLJNLBGM	90.91
09-Jun-23	0046R43ZPLJNLBGM	9.92
15-Jul-23	0046R43ZPLJNLBGM	97.98
25-Jul-23	0046R43ZPLJNLBGM	107.44
02-Aug-23	0046R43ZPLJNLBGM	349.37
18-Aug-23	0046R43ZPLJNLBGM	57.85
29-Sep-23	0046R43ZPLJNLBGM	49.59
08-Oct-23	0046R43ZPLJNLBGM	86.94
10-Oct-23	0046R43ZPLJNLBGM	45.54
10-Oct-23	0046R43ZPLJNLBGM	101.07
18-Oct-23	0046R43ZPLJNLBGM	90.91
28-Nov-23	0046R43ZPLJNLBGM	94.2
03-Dec-23	0046R43ZPLJNLBGM	99.18
14-Dec-23	0046R43ZPLJNLBGM	114.05
17-Dec-23	0046R43ZPLJNLBGM	66.12
19-Dec-23	0046R43ZPLJNLBGM	99.17
21-Dec-23	0046R43ZPLJNLBGM	105.78
21-Dec-23	0046R43ZPLJNLBGM	52.48
21-Dec-23	0046R43ZPLJNLBGM	20.25
27-Dec-23	0046R43ZPLJNLBGM	66.12
09-Jan-24	0046R43ZPLJNLBGM	74.38
09-Jan-24	0046R43ZPLJNLBGM	113.06
08-Feb-24	0046R43ZPLJNLBGM	209.63
12-Feb-24	0046R43ZPLJNLBGM	204.96
16-Feb-24	0046R43ZPLJNLBGM	37.72

Return Table
Return Date	sor_rejection_crm_id	Purchase Date
02-Aug-23	0046R43ZPLJNLBGM

[ryan_mayu · Accepted solution]

@Bigboss 

pls try this

purchase = minx(FILTER(Sell,'Sell'[Purchase Date]>'Return'[Return Date]),Sell[Purchase Date])

pls see the attachment below

[Anonymous]

Your solution is great, @ryan_mayu and @Ashish_Mathur . It worked like a charm!

Hi, @Bigboss 

Have you solved the current problem? 

Best Regards

Jianpeng Li

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in the Return table

Column = CALCULATE(MIN(Sell[Purchase Date]),FILTER(sell,Sell[sor_rejection_crm_id]=EARLIER('Return'[sor_rejection_crm_id])&&Sell[Purchase Date]>EARLIER('Return'[Return Date])))

Hope this helps.

[ryan_mayu · Accepted solution]

@Bigboss 

pls try this

purchase = minx(FILTER(Sell,'Sell'[Purchase Date]>'Return'[Return Date]),Sell[Purchase Date])

pls see the attachment below