Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
please any one can assiste me on this...
Common factores are Customer, date and system so you can create relation only on these fields...
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
115 | |
105 | |
95 | |
79 | |
72 |