Forum Discussion
Check if record from table A falls within a date range in table B without looping
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
3 Replies
- Greg_Deckler
Community Champion
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.
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.
v-chuncz-msftCommunity Support
