DAX - Compare dates and take the first date that is on or after

[united2win]

Hi, I have two tables I would like to create a calculated column in table 1 with the first date that is on or after the juri created date. Note: Dates are in dd/mm/yyyy format

table 1

Customer ID	Jurisdiction Created	Jurisdiction	Added Column (Dax) (Doc Upload)
1	01/01/2020	DE	15/01/2020
1	01/02/2020	FR	20/02/2020
1	01/03/2020	ES
2	01/03/2020	ES
2	01/04/2020	FR	02/04/2020

table 2:

Customer ID	Doc Upload Date
1	15/01/2020
1	18/01/2020
1	20/02/2020
2	02/04/2020
2	13/04/2020

[v-henryk-mstf]

Hi @united2win ,

According to your requirements, I created a measure to get the min date in table2 that meets the same month.

M1 = 
IF ( MIN(table1[Column])=MIN(table2[Column])
    ,
    CALCULATE (
        MIN ( table2[Doc Upload Date] ),
        FILTER (
            ALL ( table2 ),
            table2[Column] = MAX ( table2[Column] )
                && table2[Doc Upload Date] <= MIN ( table2[Doc Upload Date] )
        )
    ),
    BLANK()
)

It should be noted that the two columns I created get the month in the table and create a relationship

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[amitchandak · Accepted solution]

@united2win , Create a new column in table1

minx(filter(table2, table1[Customer ID] = table2[Customer ID] && table2[Doc Upload Date] >= table1[Jurisdiction Created]),table2[Doc Upload Date])