Establish Many to 1 Relationship Between Tables Based on Date Value
I have 2 tables as data sources which I brought it into PBI desktop via separate SQL queries, each query points towards a different database.
I want to create an invoice dashboard which includes:
From Table_1: InvoiceID_Unique, IdSponsor, InvoiceDate
and from Table_2: IdPartner
I want my dash to show the IdPartner which was assigned at the time the invoice was issued. In Table_2, the IdPartner associated with IdSponsor for any given time period is defined by the BeginDate and EndDate.
Example: Invoice_UniqueID 1 was issued to IdSponsor 1234 on 1/1/2019, and Table_2 shows that at that time IdSponsor 1234 was affiliated with IdPartner 1. Table_2 EndDate defines when a relationship between IdPartner and IdSponsor ended, if EndDate is NULL then the relationship is current.