Invoice and Credit notes details as per Sales person

[SSJ011]

Good Day, 

i have 2 tables one is for invoice and Credit notes with creations dates

Customer ID	Creation Date	Credit Notes	Invoices	System
331S3001453	04-Oct-21	0	1	SAM
331S3001453	07-Sep-21	0	1	SAM
331S3001453	07-Feb-21	0	1	SAM
331C5003845	17-Feb-21	1	1	SAM
1282006	07-Jul-21	0	1	SAM
1282006	11-Jan-21	0	1	SAM
331C5003845	21-Mar-21	1	1	SAM
331C5003845	04-Apr-21	0	1	SAM
331C5003845	05-Jun-21	0	1	SAM
331C5003845	04-Oct-21	1	1	SAM
331C5003845	15-Nov-21	0	1	SAM
331C5003845	15-Nov-21	0	1	SAM
331C5003845	15-Dec-21	1	1	SAM
331C0000589	04-Oct-21	0	1	SAM
1282006	04-Oct-21	0	1	SAM
1282006	04-Nov-21	1	1	SAM
1282006	04-Nov-21	0	1	SAM
331C0000589	04-Nov-21	1	1	SAM

2nd table is of sales person with the contract period 

Customer ID	System	Sales Person	Contract Period Start	Contract Period End
1282006	SAM	SSJ011	01-Jan-21	31-Oct-21
1282006	SAS	SSJ011	01-Jan-21	31-Oct-21
1282006	SEM	SSJ011	01-Jan-21	31-Oct-21
331C0000589	SAM	SSJ011	01-Jan-21	31-Oct-21
331C0000589	SEM	SSJ011	01-Jan-21	31-Oct-21
331C5003845	SAM	SGK006	01-Jan-21	31-Oct-21
331C5003845	SAS	SGK006	01-Jan-21	31-Oct-21
331S3001453	SAM	NPA124	01-Jan-21	31-Oct-21
331S3001453	SAS	NPA124	01-Jan-21	31-Oct-21
331S3001453	SEM	NPA124	01-Jan-21	31-Oct-21
1282006	SAM	SGK006	01-Nov-21	31-Dec-22
1282006	SAS	SGK006	01-Nov-21	31-Dec-22
1282006	SEM	SGK006	01-Nov-21	31-Dec-22
331C0000589	SAM	SGK006	01-Nov-21	31-Dec-22
331C0000589	SEM	SGK006	01-Nov-21	31-Dec-22
331C5003845	SAM	TPO048	01-Nov-21	31-Dec-22
331C5003845	SAS	TPO048	01-Nov-21	31-Dec-22
331S3001453	SAM	TPO048	01-Nov-21	31-Dec-22
331S3001453	SAS	TPO048	01-Nov-21	31-Dec-22
331S3001453	SEM	TPO048	01-Nov-21	31-Dec-22

Common factores in both table are Customer ID , date and system.

My requirement is how can i get the invoce and credit notes for sales persons.

[V-lianl-msft · Accepted solution]

Hi @SSJ011 ,

Please try to create measure like below:

Invoices = CALCULATE(SUM('Table'[Invoices]),FILTER('Table','Table'[Customer ID]=MAX('Table (2)'[Customer ID])&&'Table'[System]=MAX('Table (2)'[System])&&'Table'[Creation Date]>=MAX('Table (2)'[Contract Period Start])&&'Table'[Creation Date]<=MAX('Table (2)'[Contract Period End])))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SSJ011]

please any one can assiste me on this... 

[freginier]

Common factores are Customer, date and system so you can create relation only on these fields... 

[SSJ011]

Date iS in one table and we have a range where sales person was handling that customer . hence i need to calculate invoice and credit notes as per sales person