Three columns on one table, need to return the earliest

[bradytb]

I have one table that is being used (Pro Staff Info)

I have three separate columns that include a date or an N/A.

Pro Staff Info

Columns:

Initial Cert Exp (there is a date in this column if they have this type of cert, N/A if they don't)

Conditional Cert Exp (there is a date in this column if they have this type of cert, N/A if they don't)

Continuing Cert Exp( there is a date in this column if they have this type of cert, N/A if they don't)

I need a way to compare these columns and return the earliest date of the 3 columns.  

The staff can have more than one type of cert to expire.  

Ex: Sally has an initial cert expiring 6/30/2025 and a conditional cert expiring 6/30/2026.  I would need a column that just returns the earliest date (in this example  6/30/2025).  

The purpose is for the admin assistants to know when someone is expiring and starting the process to recertify.   

[Anonymous · Accepted solution]

Hi @bradytb 

Based on your needs, I have created the following table.

Then you can use the following dax to get the result you want:

Measure = VAR select_name = SELECTEDVALUE('Pro Staff Info'[Name])
RETURN
MINX(FILTER(ALL('Pro Staff Info'),'Pro Staff Info'[Name]=select_name && 'Pro Staff Info'[Date]<>BLANK()),'Pro Staff Info'[Date])

Result:

Best Regards,

Jayleny

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