Compare two date columns from table 1 and table 2, then add a column value from table 1

[MianAhsan]

Hi everyone,

I have 2 tables(Table1 and Table2) with similar column names.

Table 1 has three columns: ID, CP, and Date.

ID	CP	Dates
1	LP1	6/30/2024
1	LP2	12/30/2024
1	PP1	6/30/2025
1	PP2	12/30/2025
2	LP1	6/30/2025
2	LP2	12/30/2025
2	PP1	6/30/2026
2	PP2	12/30/2026

Table 2 also has the same columns, but we have to find CP based on the Date column here.

ID	Date	CP
1	9/15/2024
1	2/15/2025
1	5/28/2024
1	8/30/2025
1	12/25/2025
2	5/30/2025
2	9/30/2025
2	2/30/2026
2	8/30/2026
2	3/30/2026

My requirement/target is: 

The logic behind "compare two date columns based on ID, when Table 1 [Date] < Table 2 [Date] then the corresponding CP value is copied into the Table 2 CP column".

My result will look like this:

ID	Date	CP
1	9/15/2024	LP2
1	2/15/2025	PP1
1	5/28/2024
1	8/30/2025	PP2
1	12/25/2025
2	5/30/2025
2	9/30/2025
2	2/30/2026
2	8/30/2026
2	3/30/2026

I have already tried logic and created a measure, but I'm not getting the correct result:

CP = 
VAR cur_date =
    SELECTEDVALUE ('Table2'[Date])
RETURN
    CALCULATE (
        MAX ('Table1'[CP]),
        FILTER (ALL('Table1'), 'Table1'[Dates] < cur_date)
    )

 The result should be 2nd row -> PP1 and 3rd row -> PP2.

I think my logic is not correct. It should be Table 1[Date] < Table 2 [Date] value and also compare previous value too.

I hope my question is clear to everyone. 

Thank you for your support in advance.
@SamWiseOwl 

[Anonymous · Accepted solution]

Hi, @MianAhsan 

You can try the following methods.

Measure:

Nearest Date = CALCULATE(MIN('Table 1'[Dates]),FILTER(ALL('Table 1'),[Dates]>SELECTEDVALUE('Table 2'[Date])&&[ID]=SELECTEDVALUE('Table 2'[ID])))

New CP = CALCULATE(MAX('Table 1'[CP]),FILTER(ALL('Table 1'),[ID]=SELECTEDVALUE('Table 2'[ID])&&[Dates]=[Nearest Date]))

If it doesn't match your desired output, you'll also need to be told why there is no output for 5/28/2024 and 12/25/2025.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi, @MianAhsan 

You can try the following methods.

Measure:

Nearest Date = CALCULATE(MIN('Table 1'[Dates]),FILTER(ALL('Table 1'),[Dates]>SELECTEDVALUE('Table 2'[Date])&&[ID]=SELECTEDVALUE('Table 2'[ID])))

New CP = CALCULATE(MAX('Table 1'[CP]),FILTER(ALL('Table 1'),[ID]=SELECTEDVALUE('Table 2'[ID])&&[Dates]=[Nearest Date]))

If it doesn't match your desired output, you'll also need to be told why there is no output for 5/28/2024 and 12/25/2025.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MianAhsan]

Hi @Anonymous ,

I'm trying to use the "New CP" measure to create a bar chart, where for the x-axis, I plan to use the measure, and for the y-axis "increased cost" column. However, I cannot use this measure alone in any of the visuals. Any ideas or comments related to this issue? 

[Anonymous · Accepted solution]

Hi, @MianAhsan 

The X-axis cannot be put in measure. Only existing column values.

Nearest Date Column = CALCULATE(MIN('Table 1'[Dates]),FILTER('Table 1',[Dates]>EARLIER('Table 2'[Date])&&[ID]=EARLIER('Table 2'[ID])))

New CP Column = CALCULATE(MAX('Table 1'[CP]),FILTER('Table 1',[ID]=EARLIER('Table 2'[ID])&&[Dates]=[Nearest Date Column]))

It can be put into the bar chart X-axis with this column.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MianAhsan]

Hi @Anonymous ,
I checked your methods with my actual data and confirmed it with the manually created Next CP columns, and both results show me the same result.
Bundle of thanks for your support. 

Regards,

Mian

[lbendlin]

My requirement/target is: 

The logic behind "compare two date columns based on ID, when Table 1 [Date] < Table 2 [Date] 

What should happen if multiple rows from Table 1 satisfy that condition?

[MianAhsan]

That is why I want to check based on ID if Table 1[Date] < Table2[Date] (another precheck, also consider the previous value from Table 2). 
For example, ID 1 has a value in Table 2 [9/15/2024], then check all values in Table 1 [Date] -> Two values to be considered here,

1	LP1	6/30/2024
1	LP2	12/30/2024

Where Table 2 Date value comes under the LP2 category because it is less than that date but greater than LP1.
I hope this clarifies your question. 

[lbendlin]

You need two steps. First you need to find the max date in Table 1 and then you need to fetch the CP value for that date.   Max(CP) is not correct.

[MianAhsan]

Thank you for your response.
Any guide on how to follow these two steps? 

[lbendlin]

2/30/2026	 

good one !

Here is one option for the calculated column in Table 2

[MianAhsan]

Thank you for being so supportive.

Unfortunately, I cannot get the correct result with that approach.