Moving % of commission base on time

DAX DAX Calculations
1

I have internal sales people that will get the following commission:
1st year 12%
2nd year 6%
In my data model I have Date, Company Name, monthly payment, if the company started day is April 1,2021, then my sales person will get 12% until March 31,2022, after that day on April 1, 2022 the % will change to 6%, some will be always 12% because they are new and the majority will become 6% with the past of time, how you can create the calculation
image

Thank you so much in advance

2

Hello @Octavio,

Thank You for posting your query onto the Forum.

Based on the information provided for “Bonus %” i.e. “First Year = 12%” and “Second Year = 6%”.

I’ve created a demo data and here’re the formulas/measures provided for the reference -

Bonus % =

VAR _Time_Period =
DATEDIFF( SELECTEDVALUE( Data[Date] ) , TODAY() , YEAR )

VAR _Bonus_Percentage =
IF( _Time_Period < 1 , 0.12 , 0.06 )

RETURN
_Bonus_Percentage


Bonus Payment = 
[Bonus %] * SUM( Data[Payment] )

Below is the screenshot of the results provided for the reference purposes -

Final Results

I’m also attaching the working of the PBIX file for the reference purposes as well.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Note: If you’re looking for something other than this than please provide demo data and the expected results that you’re trying and achieve in an Excel file.

Thanks and Warm Regards,
Harsh

Bonus % Calculation - Harsh.pbix (18.4 KB)

1 Like
3

Arsh,
Thank you so much for your answer, it is exactly what I needed, this is amazing, also thank you for the PBIX,
Gracias, Gracias, Gracias…

4

Hello @Octavio,

Eres Bienvenido. :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh

5

Harsh,
One more question please ( si no te importa)" if you do not mind"
image
The table is working great this is the final formula:
Bonus % =

VAR _Time_Period =

DATEDIFF( SELECTEDVALUE( SFACompPay[Payment Period] ) , TODAY() , YEAR )

VAR _Bonus_Percentage =

IF( _Time_Period < 1 , 0.12 , 0.06 )

RETURN

_Bonus_Percentage
how do you eliminate data that is 0 or null meaning October, Nov. etc of this year or the company started in Aril of last year and I do not have data for January, feb, of last year, (hope my question is correct)
Thanks

6

Hello @Octavio,

It’s always a little bit difficult to provide the results without looking at the data. Can you try the below provided formula in that case -

Bonus % =

VAR _Time_Period =
DATEDIFF( SELECTEDVALUE( Data[Date] ) , TODAY() , YEAR )

VAR _Bonus_Percentage =
IF( _Time_Period < 1 , 0.12 , 0.06 )

RETURN
IF( ISBLANK( SUM( Data[Payment] ) ) ,
    BLANK(),
    _Bonus_Percentage

Note: I’ve put Payment, in your case it’ll be Total Deposits.

Hope you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

1 Like
7

I did it, now is more than perfect!!!
Thank you again…
You are the best!!!

8

Hello @Octavio, good to know you were able to fix your problem.

Kindly mark as SOLUTION the answer that solved your problem so that other users with the same concern can benefit from it.

9

image
Hello!!!
As you can see my report it is almost perfect, I just need one more modification, when I pull a company with more than a year the report bring the payments since the beginning of operations with that company and calculates 6% it is correct but I need to be able to select moth and year example:
Company started date January 1, 2020 since the company started 2020 the selection will be 6%, and bring all the months, I need to be able to select example September 2021, but since the company has more than a year the selection should be 6%