I want to take the status of the latest opportunity (created date or ID of Opportunity)

[hoanganhep]

Dear everyone, 

I have 2 tables below:

Customer

ID	Name
1	A
2	B
3	C

Opportunity

ID	Customer_ID	Status	Created Date
1	1	Won	10/10/2022
2	1	Lost	25/10/2022
3	2	Won	13/10/2022
4	2	Won	23/10/2022
5	3	Lost	13/10/2022
6	3	Won	23/10/2022

2 table relationship by: Customer.id = Opportunity.Customer_id

Now I want to create a column "Last Opp Status" at the Customer table like below:

ID	Name	Last Opp Status
1	A	Lost
2	B	Won
3	C	Won

It will take the status of the latest opportunity (created date or ID of Opportunity)

Please show me, how can I do that.

Thank you, everyone

[Samarth_18 · Accepted solution]

Hi @hoanganhep ,

You could create a column as below:-

last opp status = 
VAR latest_date =
    CALCULATE (
        MAX ( opportunity[Created Date] ),
        FILTER ( opportunity, opportunity[Customer_ID] = Customer[ID] )
    )
RETURN
    CALCULATE (
        MAX ( opportunity[Status] ),
        FILTER (
            ALL ( opportunity ),
            opportunity[Customer_ID] = Customer[ID]
                && opportunity[Created Date] = latest_date
        )
    )

Output:-

BR,

Samarth

[hoanganhep]

Thank you, sir, this is what I looking for.