Forum Discussion
Solved
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 |
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.
3 Replies
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.
Thank you! That worked.
You are welcome.
