Forum Discussion
Cumulative Sales amount beetween 2 dates
Hello Guys,
Hi guys, I hope you're all well! I'm currently creating a Sales Commission Dashboard, and I'm running into an issue that I need your help with (Thanks in advance).
I was also inspired by this tutorial https://www.youtube.com/watch?v=9hGowlw4YPc
I have a commission percentage table associated with a validity period and a range of amounts.
| Sales ID | Effective From | Effective To | Amount From | Amount To | Pourcentage |
| 0056700000AQKA4AAP | 01/01/2025 | 31/12/2025 | 0,0 € | 210 000,0 € | 1,5 |
| 0056700000AQKA4AAP | 01/01/2025 | 31/12/2025 | 210 000,2 € | 315 000,0 € | 2 |
| 0056700000AQKA4AAP | 01/01/2025 | 31/12/2025 | 315 000,2 € | 420 000,0 € | 2,5 |
| 0056700000AQKA4AAP | 01/01/2025 | 31/12/2025 | 420 000,2 € | 525 000,0 € | 3 |
I create 2 mesures
CloseDate range min =
var closedate= SELECTEDVALUE(Opportunity[CloseDate])
var currentsale= SELECTEDVALUE(Opportunity[OwnerId])
return
CALCULATE(
max('Données comissions'[Effective From]),
FILTER(
'Données comissions',
'Données comissions'[Effective From]<=closedate&&'Données comissions'[Effective To]>=closedate&&'Données comissions'[Sales ID]=currentsale
)
)which gives the minimum date of the period in which we are located. We do the same with the maximum date in the measurement
After that the issue is when I calculate the cumulative Sales amount
Cumulative Sales =
var selectedCustomer = SELECTEDVALUE(Opportunity[OwnerId])
RETURN
IF(
ISBLANK([CloseDate range min]) ||
ISBLANK([CloseDate range max]),
BLANK(),
CALCULATE(
SUM(Opportunity[Amount]),
FILTER(
ALL(Opportunity),
Opportunity[OwnerId] = selectedCustomer &&
Opportunity[CloseDate] >= [CloseDate range min] &&
Opportunity[CloseDate] <= MAX(Opportunity[CloseDate])
)
)
)
This the result that i have
| Id | CloseDate | Somme de Amount | CloseDate range min | CloseDate range max | Cumulative Sales |
| 0056700000AQKA4AAP | 31/01/2025 00:00 | 33 400 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 11435970.66 |
| 0056700000AQKA4AAP | 28/02/2025 00:00 | 15 000 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 11450970.66 |
| 0056700000AQKA4AAP | 20/03/2025 00:00 | 37 000 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 11487970.66 |
| 0056700000AQKA4AAP | 21/03/2025 00:00 | 104 500 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 11592470.66 |
| 0056700000AQKA4AAP | 31/03/2025 00:00 | 303 825 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 11896295.66 |
| 0056700000AQKA4AAP | 30/04/2025 00:00 | 172 000 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 12068295.66 |
| 0056700000AQKA4AAP | 20/05/2025 00:00 | 45 000 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 12113295.66 |
| 0056700000AQKA4AAP | 30/05/2025 00:00 | 59 650 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 12172945.66 |
| 0056700000AQKA4AAP | 16/06/2025 00:00 | 48 500 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 12221445.66 |
| 0056700000AQKA4AAP | 18/06/2025 00:00 | 225 000 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 12446445.66 |
| 0056700000AQKA4AAP | 20/06/2025 00:00 | 270 000 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 12716445.66 |
| 0056700000AQKA4AAP | 30/06/2025 00:00 | 517 500 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 13233945.66 |
| 0056700000AQKA4AAP | 11/07/2025 00:00 | 275 333 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 13509278.66 |
| 0056700000AQKA4AAP | 25/09/2025 00:00 | 30 000 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 13539278.66 |
| 0056700000AQKA4AAP | 30/09/2025 00:00 | 492 000 € | 01/01/2025 00:00 | 31/12/2025 00:00 | 14031278.66 |
And i do not understand why the formula do not calculate the good amount
Can someone help me pls ?
I'm at a loss now. You initially just wanted the cumulative sales as in your expected result but now you want to get the the percentage even for those that's been skipped. I'm attaching a sample pbix that doesnt fully address your use case as this doesnt take into account the skips but i hope this helps.
6 Replies
- danextian
Super User
Your sample data appears to be incomplete. What is the purpose of this table? Which other table interacts with it? What is your expected outcome? Please provide a complete, workable sample dataset (not an image), along with the expected result based on that data and the reasoning behind it.
- Chateauunoirr
Advocate I
danextian Thank you for your reply, okay i will try to explain in a better way,
This is my sample of data commisions of the Sale guy with the id : 0056700000AQKA4AAP but there is other rows like this one for other Sales with different commissionsSales ID Effective From Effective To Amount From Amount To Pourcentage 0056700000AQKA4AAP 01/01/2025 31/12/2025 0,0 € 210 000,0 € 1,5 0056700000AQKA4AAP 01/01/2025 31/12/2025 210 000,2 € 315 000,0 € 2 0056700000AQKA4AAP 01/01/2025 31/12/2025 315 000,2 € 420 000,0 € 2,5 0056700000AQKA4AAP 01/01/2025 31/12/2025 420 000,2 € 525 000,0 € 3 0056700000AQKA4AAP 01/01/2025 31/12/2025 525 000,2 € 630 000,0 € 4 0056700000AQKA4AAP 01/01/2025 31/12/2025 630 000,2 € 735 000,0 € 6 0056700000AQKA4AAP 01/01/2025 31/12/2025 735 000,2 € 945 000,0 € 7 0056700000AQKA4AAP 01/01/2025 31/12/2025 945 000,2 € 1 050 000,0 € 8 0056700000AQKA4AAP 01/01/2025 31/12/2025 1 050 000,2 € 999 999 999 999 999,0 € 9 I put 999 999 999 999 999 euros so that when the cumulative amount is higher than 1 050 000,2 we will take 9% of commissions.
According to the formula of the cumulative Sales the result isId CloseDate Somme de Amount CloseDate range min CloseDate range max Cumulative Sales Expeted Result 0056700000AQKA4AAP 31/01/2025 00:00 33 400 € 01/01/2025 00:00 31/12/2025 00:00 11 435 971 € 33 400 € 0056700000AQKA4AAP 28/02/2025 00:00 15 000 € 01/01/2025 00:00 31/12/2025 00:00 11 450 971 € 48 400 € 0056700000AQKA4AAP 20/03/2025 00:00 37 000 € 01/01/2025 00:00 31/12/2025 00:00 11 487 971 € 85 400 € 0056700000AQKA4AAP 21/03/2025 00:00 104 500 € 01/01/2025 00:00 31/12/2025 00:00 11 592 471 € 189 900 € 0056700000AQKA4AAP 31/03/2025 00:00 303 825 € 01/01/2025 00:00 31/12/2025 00:00 11 896 296 € 493 725 € 0056700000AQKA4AAP 30/04/2025 00:00 172 000 € 01/01/2025 00:00 31/12/2025 00:00 12 068 296 € 665 725 € 0056700000AQKA4AAP 20/05/2025 00:00 45 000 € 01/01/2025 00:00 31/12/2025 00:00 12 113 296 € 710 725 € 0056700000AQKA4AAP 30/05/2025 00:00 59 650 € 01/01/2025 00:00 31/12/2025 00:00 12 172 946 € 770 375 € 0056700000AQKA4AAP 16/06/2025 00:00 48 500 € 01/01/2025 00:00 31/12/2025 00:00 12 221 446 € 818 875 € 0056700000AQKA4AAP 18/06/2025 00:00 225 000 € 01/01/2025 00:00 31/12/2025 00:00 12 446 446 € 1 043 875 € 0056700000AQKA4AAP 20/06/2025 00:00 270 000 € 01/01/2025 00:00 31/12/2025 00:00 12 716 446 € 1 313 875 € 0056700000AQKA4AAP 30/06/2025 00:00 517 500 € 01/01/2025 00:00 31/12/2025 00:00 13 233 946 € 1 831 375 € 0056700000AQKA4AAP 11/07/2025 00:00 275 333 € 01/01/2025 00:00 31/12/2025 00:00 13 509 279 € 2 106 708 € 0056700000AQKA4AAP 25/09/2025 00:00 30 000 € 01/01/2025 00:00 31/12/2025 00:00 13 539 279 € 2 136 708 € 0056700000AQKA4AAP 30/09/2025 00:00 492 000 € 01/01/2025 00:00 31/12/2025 00:00 14 031 279 € 2 628 708 €
Is that much clearer for you?- danextian
Super User
I was looking for the date used againt the commission table. For example, what number did you compare against the comission table to be able to come up with 33400 in the first row of your expected result. What you posted still is the commission table. Anyway, please try the attached pbix.