Unique many to many relationship

[eddieatchisonam]

Greetings! 

I have 2 data sources. 

1 table has a unique account number, but non unique name, and some additional dollar amounts, etc. 
2 table has many non unique account numbers, and non unique names, additional dollars, etc. 

Table 1 is the accounts list. So for example: 

Client Name	Account Number	Client Reference Number	Assign Date	Assigned Amount	Principal Due	Sts	Name1	Client	Judgment Date	Tie Series	Total Due
APC	3750	X2861466	1/7/2016	17.53	0	PIF	JONES,MIKE	APCCOM		3862	0
APC	3751	X3094513	1/7/2020	582.41	0	PIF	JONES,MIKE	APCCOM		0	123.58
APC	3752	X3179447	1/7/2016	9375.5	9375.5	XST	SMITH,SUSIE	APC-SP		3863	15867.72
APC	3753						FREDERICK,ALLISON
APC	3754	X2871317	1/7/2016	2818.37	0	PIF	SCOTT,FREDERICK	APCCOM		3865	313.3
APC	3755	X2628675	1/7/2016	277.69	0.2	PIF	SCOTT,FREDERICK	APCCOM		3888	43.06
APC	3756	X2886208	1/8/2020	586.64	0	PIF	SCOTT,FREDERICK	APCCOM		0	143.61
AAPC	3757	X3043585	1/7/2016	250.91	0	PIF	BART,RICHARD	APCCOM		0	55.65
APC	3758	X3061751	1/7/2016	562.36	0	PIF	BART,RICHARD	APCCOM		0	121.64

Table is the transaction list. 

Account Number	Transaction Code	Amount	Transaction Date
3750	25	33.1	4/30/2020
3750	415	96.19	1/2/2020
3754	415	96.19	1/8/2020
3756	415	91.99	1/21/2020
3757	415	96.19	1/23/2020

I'm having trouble I guess getting the two to link. What I really need to find right now is: 

The sum of transaction_Amount - IF payments were made to accounts that were assigned in a previous year, but the same individual was assigned in 2020. 

So Mike Jones was assigned in 2016 and 2020. His transactions were only applied to the 2016 accounts, but since he was assigned other account in 2020, i should get a total of 129.29 for him. 
Whereas:  frederick scott was assigned in 2016 , twice, and 2020. his transactions were applied to a 2016 and the 2020. so i should only get 96.19 for the transaction amount made in 2020 that was applied to the account assigned in 2016.

And no others should show in the total, as they didn't have assigned in 2020 and payments applied to previous assigned dates. 

I've tried explaining this before, and no luck. I hope for help as I've spent at least 30-40 hours trying to figure this out. 


I believe it has something to do with the linking of the many to many as even the simple visualtizations i try to do don't work out correctly. 

Any help would be appreciated. Thank you. 

[eddieatchisonam]

i'll break this down to smaller projects and see what i can do. thanks! 

[amitchandak]

@eddieatchisonam , While I am not able to get the complete problem. It needs to be broken into smaller problems.

One thing is that you need a common account table. And a date table joined with both of these table

https://www.youtube.com/watch?v=Bkf35Roman8

https://www.seerinteractive.com/blog/join-many-many-power-bi/

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.