Select specific date based on filter criteria

[Craines]

Hi, I have two tables containing student application data as follows:

StudentTable

StudentId	Status	ChangeDate	Deferral?	AppDate
Student1	A1	01/10/2020	TRUE	01/10/2020
Student1	Os2	05/10/2020	TRUE	01/10/2020
Student1	Oz5	06/10/2020	TRUE	01/10/2020
Student2	Aa5	07/10/2020	FALSE
Student2	R33	11/10/2020	FALSE
Student3	A7h	02/10/2020	TRUE	02/10/2020
Student3	O1	04/10/2020	TRUE	02/10/2020
Student3	AC8	05/10/2020	TRUE	02/10/2020
Student4	A1	06/10/2020	TRUE	06/10/2020
Student4	O3	09/10/2020	TRUE	06/10/2020
Student4	O3	10/10/2020	TRUE	06/10/2020

StatusTable (is much larger - only showing relevant codes) - linked to StudentTable on Status

Status	Application	Offer	Acceptance
A1	1	0	0
Aa5	1	0	0
A7h	1	0	0
Os2	1	1	0
Oz5	1	1	0
O1	1	1	0
O3	1	1	0
AC8	1	1	1
R33	1	0	0

I have calculated the AppDate column using the following:

DeferAppDate = IF( 'StudentTable'[Deferral?] = True, MINX( FILTER('StudentTable', 'StudentTable'[StudentId] = EARLIER( 'StudentTable'[StudentId])), 'StudentTable'[ChangeDate]))

I would now like a column which picks up the date at which the first offer is made to look as follows:

StudentId	Status	ChangeDate	Deferral?	AppDate	OfferDate
Student1	A1	01/10/2020	TRUE	01/10/2020	05/10/2020
Student1	Os2	05/10/2020	TRUE	01/10/2020	05/10/2020
Student1	Oz5	06/10/2020	TRUE	01/10/2020	05/10/2020
Student2	Aa5	07/10/2020	FALSE
Student2	R33	11/10/2020	FALSE
Student3	A7h	02/10/2020	TRUE	02/10/2020	04/10/2020
Student3	O1	04/10/2020	TRUE	02/10/2020	04/10/2020
Student3	AC8	05/10/2020	TRUE	02/10/2020	04/10/2020
Student4	A1	06/10/2020	TRUE	06/10/2020	09/10/2020
Student4	O3	09/10/2020	TRUE	06/10/2020	09/10/2020
Student4	O3	10/10/2020	TRUE	06/10/2020	09/10/2020

I am struggling as this is neither the MAX or MIN date, so I haven't been able to use the same approach as for the AppDate (which will always be the first date), and wonder if the approach needs to involve "Offer = 1" from the StatusTable?

Thanks in advance!

[BA_Pete · Accepted solution]

Hi @Craines ,

Please try this measure:

offerDate = 
CALCULATE(
    MIN(StudentTable[ChangeDate]),
    CONTAINSSTRING(StudentTable[Status], "O"),
    ALLEXCEPT(StudentTable, StudentTable[StudentId])
)

This gives me the following ouput:

Pete

[Craines]

This worked perfectly, thanks @BA_Pete !