Establish Many to 1 Relationship Between Tables Based on Date Value

[Anonymous]

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.

Any thoughts on how to accomplish this?  Thanks!

Table_1:

InvoiceID_Unique	IdSponsor	InvoiceDate
1	1234	1/1/2019
2	1234	2/15/2019
3	3567	11/8/2019
4	2345	4/15/2019
5	2345	6/15/2019

Table_2:

IdSponsor	IdPartner	BeginDate	EndDate
1234	1	10/1/2018	2/1/2019
1234	2	2/2/2019	NULL
3567	1	10/5/2019	NULL
2345	32	1/1/2015	5/1/2019
2345	45	5/2/2019	NULL

Desired Result:

InvoiceID_Unique	IdSponsor	InvoiceDate	IdPartner
1	1234	1/1/2019	1
2	1234	2/15/2019	2
3	34567	11/8/2019	1
4	2345	4/15/2019	32
5	2345	6/15/2019	45

[mwegener · Accepted solution]

Hi @Anonymous ,

look at this.

PBIX

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[amitchandak]

Refer, the way current employee has been created in the same way it can be doen

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Outherwise in you invoice table create one measure

minx(filter(table2,table1[col1] =table2[col1] && table1[col2] =table2[col2]),table1[col4])

while giving condition on close use isblank for null value

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Having difficulty creating the measure, table 1 isn't recognized in the formula, though table 2 is.   I'm trying to enter the formula in data view, is that the correct spot?