Calculated Column Based on the max of a date in a different table

[mcantos]

Hello, I hope this time you can also help me.

I have two related tables and I need to create a calculated column depending on one condition in the second table.

I need to add the new column in the Table1 Opportunities with the Stage name of the Table2 Stages with the Max  value on "Stage first starting date" 

Table 1. Opportunities

Opportunity ID	Opportunity name	Status	Stage name
1002	ABCD	Active
1003	EFGH	Declined
1004	IJKL	On Hold

Table 2 Stages

Opportunity ID	Stage name	Start date
1002	Initial	1/2/2022
1002	Triage	1/7/2023
1002	Discovery	4/5/2023
1002	Negotiation	4/5/2022
1003	Screening	1/12/2020
1003	Triage	3/5/2022
1003	Full Assessment	3/5/2024

The result I'm expecting in Table 1 is :

Opportunity ID	Opportunity name	Status	Stage name
1002	ABCD	Active	4/5/2023
1003	EFGH	Declined	3/5/2024
1003	IJKL	On Hold

Thank you so much in advance.

[bhanu_gautam]

@mcantos , Use this measure and your output is incorrect as First Max date is 1/07/2023 for oppurtunity id 1002

Max Start Date =

VAR MaxDate =

    CALCULATE(

        MAX('Stages'[Start date]),

        FILTER(

            'Stages',

            'Stages'[Opportunity ID] = 'Opportunities'[Opportunity ID]

        )

    )

RETURN

    MaxDate

[mcantos]

Thank you for your replay, 

Actually I made a mistake in the post. The result I'm expecting is th stage name instead of the date.

Sorry and thank you again. 

[bhanu_gautam · Accepted solution]

@mcantos ,Please find the updated measure

DAXMax Stage Name =

VAR MaxDate =

    CALCULATE(

        MAX(Stages[Start date]),

        FILTER(

            Stages,

            Stages[Opportunity ID] = Opportunities[Opportunity ID]

        )

    )

RETURN

    CALCULATE(

        MAX(Stages[Stage name]),

        FILTER(

            Stages,

            Stages[Opportunity ID] = Opportunities[Opportunity ID] &&

            Stages[Start date] = MaxDate

        )

    )

[mcantos]

Thanks a lot @bhanu_gautam , that was fast. 

It worked!! But now I have a situation where the max of the date does not work for some records and I have to select the second max date of the stage table.

Should I mark this as a solution and open a new ticket ?

Thank you again! 

[bhanu_gautam]

Yes you should open new ticket with complete scenario , where you need to use second highest value 

[Arul]

@mcantos ,

I assume Opportunity ID is the relationship column, Please refer the below,

Stage Name = CALCULATE( 
    MAX(Stages[Start date]))