Show the earliest date from multiple dates in new column using DAX

[POSPOS]

Hi All,

I have a requirement to show a new column based on two other columns. 

Scenario: For every contract number, check for the 'Contract status' where status="Negotiation" and then display the first or the earliest contract status date in new column.
Note: 'Status=Negotiation' will be repeating for each conract no. and we want to show the first occurance date.

Sample data:

Contract No.	Contract Status	Contract Status Date
22	Submitted	1/5/2021
22	In-Progress	1/8/2021
22	Negotiation	1/15/2021
22	Negotiation	1/25/2021
22	Negotiation	1/27/2021
22	Approved	1/30/2021
23	Submitted	5/19/2022
23	In-Progress	5/22/2022
23	Negotiation	5/27/2022
23	Negotiation	5/29/2022
23	Negotiation	5/30/2022
23	Approved	5/30/2022

Expected output:

Contract No.	Negotiation Status	First Negotiation Status Date
22	Negotiation	1/15/2021
23	Negotiation	5/27/2022

Could someone please advise on how this can be achieved using DAX as I will have to use this to derive other calculated columns?

Thank you.

[Ritaf1983 · Accepted solution]

Hi @POSPOS 
Try the calculated column with dax:

first negotiation = if('Table'[Contract Status]="Negotiation",CALCULATE(min('Table'[Contract Status Date]),ALLEXCEPT('Table','Table'[Contract No.]),'Table'[Contract Status]="Negotiation"))
You can download the sample file from This link 

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

[POSPOS]

Thank you @Ahmedx @Ritaf1983  - Solution provided has worked.

[Ahmedx · Accepted solution]

pls try this

m2 = 
VAR _t1 = ALLEXCEPT('Table','Table'[Contract No.])
VAR _t2 = MAX('Table'[Contract Status])="Negotiation"
VAR _Results = CALCULATE(MIN('Table'[Contract Status Date]),_t1,'Table'[Contract Status]="Negotiation")
RETURN IF(_t2,
_Results)

[Ritaf1983 · Accepted solution]

Hi @POSPOS 
Try the calculated column with dax:

first negotiation = if('Table'[Contract Status]="Negotiation",CALCULATE(min('Table'[Contract Status Date]),ALLEXCEPT('Table','Table'[Contract No.]),'Table'[Contract Status]="Negotiation"))
You can download the sample file from This link 

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