Forum Discussion
Select specific date based on filter criteria
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!
Hi Anonymous ,
Please try this measure:
offerDate = CALCULATE( MIN(StudentTable[ChangeDate]), CONTAINSSTRING(StudentTable[Status], "O"), ALLEXCEPT(StudentTable, StudentTable[StudentId]) )This gives me the following ouput:
Pete
