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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors