Check if record from table A falls within a date range in table B without looping

[ncl35]

Hello

I'm new to PowerBI and would like some advice whether it is possible to determine if a date from table A falls within two dates in table B.  Here is an example of the tables:

TABLE A:

STUDENT	ACADEMIC YEAR	COURSE	REGISTRATION START DATE	REGISTRATION END DATE
S123	2017	C001	05/09/2017	10/12/2017
S123	2017	C002	02/11/2017	15/12/2017
S123	2017	C001	13/01/2018	30/03/2018
S456	2017	C001	01/09/2017	31/07/2018
S789	2017	C001	10/09/2017	31/07/2018

TABLE B:

STUDENT	ACADEMIC YEAR	COURSE	LOGIN DATE	XX  ACTIVE REGISTRATION XX
S123	2017	C001	10/09/2017	YES
S123	2017	C002	05/12/2017	YES
S123	2017	C001	09/01/2018	NO
S456	2017	C001	01/09/2017	NO

So, I would like XX ACTIVE REGISTRATION XX to be determined by comparing the login date with and checks whether it falls within REGISTRATION START DATE and  REGISTRATION END DATE.

( I would concatenate STUDENT, YEAR and COURSE and loop at table A to check whether login date falls with registration start and end date)

Any advice or potential solution would be much appreciated.

Thanks

[v-chuncz-msft · Accepted solution]

@ncl35,

Here is an example for your reference.

[Greg_Deckler]

My initial thoughts on this is that there is nothing that ties the 3rd rows together really in terms of checking if it falls within those particular dates versus, for example, the first row.

[ncl35]

Thanks for your prompt response.

One possible logic I was thinking of was for each entry in TABLE-B I would loop at TABLE-A macthing on STUDENT, YEAR and COURSE and then an 'if statement' to check whether TABLE-B Login Date falls with the Regitration Start and End Date of TABLE-A.

However, I can't see that I can apply that logic.