Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
united2win
Helper III
Helper III

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

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 IDJurisdiction CreatedJurisdictionAdded Column (Dax) (Doc Upload)
101/01/2020DE15/01/2020
101/02/2020FR20/02/2020
101/03/2020ES 
201/03/2020ES 
201/04/2020FR02/04/2020

 

table 2:

Customer IDDoc Upload Date
115/01/2020
118/01/2020
120/02/2020
202/04/2020
213/04/2020
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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])

View solution in original post

2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

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()
)

v-henryk-mstf_0-1612864105080.png

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

v-henryk-mstf_1-1612864184735.png

 

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
Super User
Super User

@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])

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors