How to have column lookup latest value in table 2 relative to date field in table 1

[merwijas]

I've updated my original post to better reflect the problem.

I have two tables, Table 1 that tracks changes in a customer’s level over time and Table 2 that tracks changes to customers anticipated dates of reaching a level. There are lots of customer IDs but for simplicity in the example I will only show one.

Table 1

Date                      Customer ID       level

1/1/19                   1000                       1

1/15/19                 1000                       2

2/1/19                   1000                       3

3/1/19                   1000                       4

Table 2

Date                      Customer ID       Anticipated date for level 4

1/1/19                   1000                       2/1/19                  

2/15/19                   1000                       2/20/19

2/25/19                   1000                       3/1/19

How can I create a column in the table 1 that returns the expected date for reaching level 4 from table 2 at the time of the date value in table 1? The calculated column in table 1 would look like this:

Date                      Customer ID       level      Expected level 4 date

1/1/19                   1000                       1              2/1/19  

1/15/19                 1000                       2              2/1/19  

2/1/19                   1000                       3              2/1/19                  

3/1/19                   1000                       4              3/1/19

Many thanks for any suggestions!

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=LOOKUPVALUE(Table2[Anticipated date for Level 4],Table2[Date],CALCULATE(MAX(Table2[Date]),FILTER(Table2,Table2[Customer ID]=EARLIER(Table1[Customer ID])&&Table2[Date]<=EARLIER(Table1[Date]))),Table2[Customer ID],Table1[Customer ID])

Hope this helps.

[merwijas]

@Ashish_Mathur  Thank you so much!!! That worked perfectly.

[Ashish_Mathur]

You are welcome.

[mahoneypat]

In the query editor, on the query for Table 1, you can do a merge step joining on both the Date and Customer columns.  This will bring in a table of the corresponding rows from Table 2 into Table 1.  You then just need to expand the Anticipated Date for Level 4 column.

You can also do it with DAX but this approach with query may be a little easier.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[merwijas]

@mahoneypat  

Thank you for your response. I see I made my example tables too simplistic, so I changed my original post to better show the difficulty, which is that the dates don’t necessarily align between the two tables. I can’t merge on Date and Customer in the query editor because the dates are different in most cases. Changes in expected dates in table 2 occur on different dates than when levels changes occur in table 1. I need the most recent predicted date from table 2 at the time that a level change occurred in table 1 for each customer.

Thanks!