How do I compare 1 date with several dates in 2 tables to find the minimum

[JL0101]

The goal is to categories table 1 with the the dates of table 2 by whichever date is closer in table 2. How can this be achieved in power query? I have provided the Dax script below.

Table 1: model with version

Model Version Number

EI1	17/01/2023
EI1	19/01/2023
EI1	28/01/2023
EI1	02/02/2023
EI2	05/02/2023
EI2	26/02/2023
EI2	04/03/2023
EI2	08/03/2023

Table 2: Dates

Date

13/01/23

27/01/23

10/02/23

24/02/23

07/03/23

21/03/23

05/04/23

For example

Table 3: model with version categorized

EI1	17/01/2023		27/01/23
EI1	19/01/2023		27/01/23
EI1	28/01/2023		10/02/23
EI1	02/02/2023		10/02/23
EI2	05/02/2023		10/02/23
EI2	26/02/2023		07/03/23
EI2	04/03/2023		07/03/23

etc...

Below is the Code that works how can I write it in power query?

DateAg = CALCULATE ( MIN ( 'Date'[DateList]), FILTER ( 'Date', 'Date'[DateList] >= 'EI'[Version] ) )

[JL0101]

Perfect, thank you!

[Payeras_BI · Accepted solution]

Hi @JL0101 ,

A possible solution:

Make sure both Version and DateList columns are set to type date.