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.
Example:
- 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.
Ty,
Daniel
