Skip to main content
Showing results for 
Search instead for 
Did you mean: 

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.

Not applicable

Calculating pondered quota achievement for N months.

Hey guys,


I'm with a challenge here, and I'm not able to solve it with DAX so far. Thanks in advance for any help provided.


I have 2 main fact tables: Sales (with won date[date of commercial commitment], payment date[the date my customer has paid me], and the name of the salesperson) and Monthly Quota table (each salesperson quota for each month, that considers won date), all they have a date column like MMM-YYYY where I can relate the sum of sales won in a month with that month's quota.

I've as well two dim tables: Date and "Comission Rules".


The comission rule is the same for all salesperson, is based on quota achievement Ex.: having $1.000.000 of sales and $1.100.000 of quota, it means I have 110% or 1.1 of achievement, for a given salesperson on a given month. (it's a function, a business rule that is not proportional, ex.: 30% of quota achievement = 0% comission, 80% of quota = 80% of comission, and 110% of quota = 130% of comission). I have a table for this but I could also usa a "big IF", it's not that complex.


The output I need is the amount of comission that my backoffice need to pay for each salesperson that month. It's given by the sum of the comission from all sales that were paid in the earlier month for that salesperson. I don't care here about the won date, there are sales with a lot of different won dates being paid at the same month. But I need to know the quota achievement on each of that months.



- We are in October 2021 (M0), so I want to fetch all deals from Sales table that customers paid in Sep-2021 (M-1). I'll slice a date filter fot that.

- Among the sales paid in Sep-2021, there are many different won dates (Ex.: Sep, Aug, Jul, and maybe more (M-1, M-2,... M-N)).

- I need to assure for each of those months if the quota is achieved, and how much. This have to happen for "N" months, since I may have any number of months here. The quotas are different per month and different per salesperson, can't use averages here.



- The output I need is something like having a date slicer selecting "won date" (format MMM-YYYY), and returning something like:


= Divide((SUMX(sales), Filter (paid_date = SEP-2021)), (SUM(quota), FILTER (quota_date = SEP-2021))) + sum the same for one month earlier, and the month before, and so on until the end.  



From here I believe I can do the rest of the stuff, but I didn't manage to operate this so far.

I'm not sure if it may be done with DAX, and I'm ok using several measures or creating new columns / tables if needed.

Just let me know if I was no clear on something, and thanks everyone in advance.




Super User
Super User

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors