Forum Discussion
Related column matched on time period
Super Userkevin_lie ,Can you share sample data and sample output.
amitchandak sure, thanks for the reply!
So the table_1 would contain the dates at which a specific card is issued to a person
| Date | Card_id | Card_owner |
| 1/1/2020 | crd_01 | Pete |
| 1/1/2020 | crd_02 | David |
| 5/2/2020 | crd_01 | Marie |
| 14/2/2020 | crd_01 | Jack |
| 15/2/2020 | crd_02 | John |
Table_2 has trips made on all the cards. Here the added column 'card_owner' should look to table_1 to see who had the specific card in use at the time the trip was made. So all trips made on CRD_0001 from 1-1-2020 up to and including 4-2-2020 should by ascribed to Pete, from 5-2-2020 up to and including 13-4-2020 to Marie and after that Jack. So the output would look like this:
| Trip_id | Date | Card_id | Card_owner (desired output) |
| 1 | 02/01/2020 | crd_01 | Pete |
| 2 | 02/01/2020 | crd_02 | David |
| 3 | 05/01/2020 | crd_01 | Pete |
| 4 | 06/01/2020 | crd_01 | Pete |
| 5 | 07/01/2020 | crd_02 | David |
| 6 | 07/02/2020 | crd_01 | Marie |
| 7 | 08/02/2020 | crd_02 | David |
| 8 | 16/04/2020 | crd_01 | Jack |
| 9 | 17/4/2020 | crd_01 | Jack |
| 10 | 17/4/2020 | crd_02 | John |
Thanks again for taking the time to reply. I'm new to DAX and this forum, so please let me know if I'm supposed to formulate my questions a certain way.
Sorry amitchandak but I don't understand why the tables format like this? I keep getting html errors when trying to post, any idea what I'm doing wrong?