A calculated column or custom column that compares two date columns output which date it is closest

[BryceJones]

Hello all,

I need to create either a calculated column in DAX or a custom column in PowerQuery that compare two date columns in two tables. The output is a column that has the value in the second table that is closest to the first table that is greater than the first table. I am unable to upload the file directly. However, this is the first table: 

Here is the second: 

And here is the desired output with the comparison being done on the Begin Date in the first table and the Date column in the second 

[Bibiano_Geraldo · Accepted solution]

Hi @BryceJones ,

You can achieve your goal by creating a new calculated column in first table with this DAX:

Closest Date = 
VAR CurrentBeginDate = 'FirstTable'[Begin Date]
RETURN
    MINX(
        FILTER(
            'SecondTable',
            'SecondTable'[Date] > CurrentBeginDate
        ),
        'SecondTable'[Date]
    )

Your output will look like this:

[BryceJones]

@Bibiano_Geraldo  

Thank you so much. This worked!

[amitchandak]

@BryceJones , A new column in Table 1

Maxx(filter(Table2, Table2[Date] >= Table1[Start Date] && Table2[Date] <= Table1[End Date]) , Table2[Date])

[BryceJones]

@amitchandak ,

Thank you. To clarify Begin Date and Date are the only dates in my scenario that need this comparison performed on.