Forum Discussion

Chateauunoirr's avatar
Chateauunoirr
Icon for Advocate I rankAdvocate I
1 year ago
Solved

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 IDEffective FromEffective ToAmount FromAmount ToPourcentage
0056700000AQKA4AAP01/01/202531/12/20250,0 €210 000,0 €1,5
0056700000AQKA4AAP01/01/202531/12/2025210 000,2 €315 000,0 €2
0056700000AQKA4AAP01/01/202531/12/2025315 000,2 €420 000,0 €2,5
0056700000AQKA4AAP01/01/202531/12/2025420 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

[CloseDate range max]

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 

IdCloseDateSomme de AmountCloseDate range minCloseDate range maxCumulative Sales
0056700000AQKA4AAP31/01/2025 00:0033 400 €01/01/2025 00:0031/12/2025 00:0011435970.66
0056700000AQKA4AAP28/02/2025 00:0015 000 €01/01/2025 00:0031/12/2025 00:0011450970.66
0056700000AQKA4AAP20/03/2025 00:0037 000 €01/01/2025 00:0031/12/2025 00:0011487970.66
0056700000AQKA4AAP21/03/2025 00:00104 500 €01/01/2025 00:0031/12/2025 00:0011592470.66
0056700000AQKA4AAP31/03/2025 00:00303 825 €01/01/2025 00:0031/12/2025 00:0011896295.66
0056700000AQKA4AAP30/04/2025 00:00172 000 €01/01/2025 00:0031/12/2025 00:0012068295.66
0056700000AQKA4AAP20/05/2025 00:0045 000 €01/01/2025 00:0031/12/2025 00:0012113295.66
0056700000AQKA4AAP30/05/2025 00:0059 650 €01/01/2025 00:0031/12/2025 00:0012172945.66
0056700000AQKA4AAP16/06/2025 00:0048 500 €01/01/2025 00:0031/12/2025 00:0012221445.66
0056700000AQKA4AAP18/06/2025 00:00225 000 €01/01/2025 00:0031/12/2025 00:0012446445.66
0056700000AQKA4AAP20/06/2025 00:00270 000 €01/01/2025 00:0031/12/2025 00:0012716445.66
0056700000AQKA4AAP30/06/2025 00:00517 500 €01/01/2025 00:0031/12/2025 00:0013233945.66
0056700000AQKA4AAP11/07/2025 00:00275 333 €01/01/2025 00:0031/12/2025 00:0013509278.66
0056700000AQKA4AAP25/09/2025 00:0030 000 €01/01/2025 00:0031/12/2025 00:0013539278.66
0056700000AQKA4AAP30/09/2025 00:00492 000 €01/01/2025 00:0031/12/2025 00:0014031278.66


And i do not understand why the formula do not calculate the good amount 
Can someone help me pls ? 



  • danextian's avatar
    danextian
    1 year ago

    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

  • Hi Chateauunoirr 

     

    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's avatar
      Chateauunoirr
      Icon for Advocate I rankAdvocate 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 commissions

       

      Sales IDEffective FromEffective ToAmount FromAmount ToPourcentage
      0056700000AQKA4AAP01/01/202531/12/20250,0 €210 000,0 €1,5
      0056700000AQKA4AAP01/01/202531/12/2025210 000,2 €315 000,0 €2
      0056700000AQKA4AAP01/01/202531/12/2025315 000,2 €420 000,0 €2,5
      0056700000AQKA4AAP01/01/202531/12/2025420 000,2 €525 000,0 €3
      0056700000AQKA4AAP01/01/202531/12/2025525 000,2 €630 000,0 €4
      0056700000AQKA4AAP01/01/202531/12/2025630 000,2 €735 000,0 €6
      0056700000AQKA4AAP01/01/202531/12/2025735 000,2 €945 000,0 €7
      0056700000AQKA4AAP01/01/202531/12/2025945 000,2 €1 050 000,0 €8
      0056700000AQKA4AAP01/01/202531/12/20251 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 is 

      IdCloseDateSomme de AmountCloseDate range minCloseDate range maxCumulative SalesExpeted Result
      0056700000AQKA4AAP31/01/2025 00:0033 400 €01/01/2025 00:0031/12/2025 00:0011 435 971 €33 400 €
      0056700000AQKA4AAP28/02/2025 00:0015 000 €01/01/2025 00:0031/12/2025 00:0011 450 971 €48 400 €
      0056700000AQKA4AAP20/03/2025 00:0037 000 €01/01/2025 00:0031/12/2025 00:0011 487 971 €85 400 €
      0056700000AQKA4AAP21/03/2025 00:00104 500 €01/01/2025 00:0031/12/2025 00:0011 592 471 €189 900 €
      0056700000AQKA4AAP31/03/2025 00:00303 825 €01/01/2025 00:0031/12/2025 00:0011 896 296 €493 725 €
      0056700000AQKA4AAP30/04/2025 00:00172 000 €01/01/2025 00:0031/12/2025 00:0012 068 296 €665 725 €
      0056700000AQKA4AAP20/05/2025 00:0045 000 €01/01/2025 00:0031/12/2025 00:0012 113 296 €710 725 €
      0056700000AQKA4AAP30/05/2025 00:0059 650 €01/01/2025 00:0031/12/2025 00:0012 172 946 €770 375 €
      0056700000AQKA4AAP16/06/2025 00:0048 500 €01/01/2025 00:0031/12/2025 00:0012 221 446 €818 875 €
      0056700000AQKA4AAP18/06/2025 00:00225 000 €01/01/2025 00:0031/12/2025 00:0012 446 446 €1 043 875 €
      0056700000AQKA4AAP20/06/2025 00:00270 000 €01/01/2025 00:0031/12/2025 00:0012 716 446 €1 313 875 €
      0056700000AQKA4AAP30/06/2025 00:00517 500 €01/01/2025 00:0031/12/2025 00:0013 233 946 €1 831 375 €
      0056700000AQKA4AAP11/07/2025 00:00275 333 €01/01/2025 00:0031/12/2025 00:0013 509 279 €2 106 708 €
      0056700000AQKA4AAP25/09/2025 00:0030 000 €01/01/2025 00:0031/12/2025 00:0013 539 279 €2 136 708 €
      0056700000AQKA4AAP30/09/2025 00:00492 000 €01/01/2025 00:0031/12/2025 00:0014 031 279 €2 628 708 €


      Is that much clearer for you?

      • danextian's avatar
        danextian
        Icon for Super User rankSuper 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.