cancel
Showing results for
Did you mean:  Helper I

## Extract dates from separate tables

I need to create a new column in my data showing the due date for items however some of my items have a due date where the value of the due date is in a seperate table. In the original table there is no date and instead an identifier is shown and the other table shows the value (Date) of that identifier.

Below is a sample of the data. Can anyone advise on the formula required to create a new column in Table A with all the due dates?

Table A

 Item Serial Number Instance 1 A-004 18/10/2024 2 A-006 25/02/2026 3 A-001 A 4 A-002 A 5 A-001 B 6 A-001 A 7 A-002 B 8 A-002 A 9 A-002 B

Table B

 Serial Number Instance Date A-001 A 31/01/2024 A-001 B 31/01/2025 A-001 C 31/01/2026 A-002 A 15/06/2024 A-002 B 20/08/2025 A-002 C 31/01/2026

1 ACCEPTED SOLUTION  Super User

Hi,

Write this calculated column formula in Table1

``Column = CALCULATE(MAX(Table2[Date]),FILTER(Table2,Table2[Serial Number]=EARLIER(Table1[Serial Number])&&Table2[Instance]=EARLIER(Table1[Instance])))``

Hope this helps. Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3  Super User

Hi,

Write this calculated column formula in Table1

``Column = CALCULATE(MAX(Table2[Date]),FILTER(Table2,Table2[Serial Number]=EARLIER(Table1[Serial Number])&&Table2[Instance]=EARLIER(Table1[Instance])))``

Hope this helps. Regards,
Ashish Mathur
http://www.ashishmathur.com  Helper I

Thank you! That worked.  Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com   