Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
pls try this
Proud to be a Super User!
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.
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
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.
pls try this
Proud to be a Super User!
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |