Breakeven point for salesman DAX, Urgent!!

[Anonymous]

Hi,

Im kind of new with Power Bi (less than a year using it) and DAx language.

Currently in my company Im traying to model in Power Bi several KPIs, in this case Im having issues with 1 specifically. Employee Breakeven, that means the number of months it takes for each employee to breakeven, that is to determine the number of months in which employees salary equals to the income generated for the company.

Here is a snapshot of the data that I have and the current structure.

(Of course they are mock ups since I cannot display companies true information)

|Salesman| Salesman Hired Date| Terminated Date| Account Average| Account Balance| Total Fees| Account Open Date | Account Close Date|
Peter P      2/1/2018      5/5/2019      1234      $10,000.00      25000      3/3/2018
Peter P      2/1/2018      5/5/2019      2345      $15,000.00      30000      2/16/2018      6/15/2019
Peter P      2/1/2018      5/5/2019      3456      $20,000.00      45000      3/18/2018
Tony S      5/6/2018                           4567      $25,000.00      46000      6/5/2018
Tony S      5/6/2018                           5678      $30,000.00      38000      5/21/2018
Tony S      5/6/2018                           6789      $45,000.00      42000      6/20/2018
Tony S      5/6/2018                           7891      $50,000.00      46000      6/5/2018
Bruce B     1/1/2019                          8912      $65,000.00      49000      1/16/2019
Bruce B     1/1/2019                          9123      $75,000.00      60000      2/15/2019
Bruce B     1/1/2019                         4568      $80,000.00      20000      1/31/2019
Bruce B     1/1/2019                        1594      $15,000.00      30000      1/16/2019

And then I have Payroll

Salesman Paydate Amount
Peter P 2/28/2018 $1,667
Peter P 3/30/2018 $1,667
Peter P 4/29/2018 $1,667
Peter P 5/29/2018 $1,667
Peter P 6/28/2018 $1,667
Peter P 7/28/2018 $1,667
Peter P 8/27/2018 $1,667
Peter P 9/26/2018 $1,667
Peter P 10/26/2018 $1,667
Peter P 11/25/2018 $1,667
Peter P 12/25/2018 $1,667
Peter P 1/24/2019 $1,667
Peter P 2/23/2019 $1,667
Peter P 3/25/2019 $1,667
Peter P 4/24/2019 $1,667
Peter P 5/24/2019 $1,667
Peter P 6/23/2019 $1,667
Peter P 7/23/2019 $1,667
Peter P 8/22/2019 $1,667
Peter P 9/21/2019 $1,667
Peter P 10/21/2019 $1,667
Tony S 5/30/2018 $1,667
Tony S 6/29/2018 $1,667
Tony S 7/29/2018 $1,667
Tony S 8/28/2018 $1,667
Tony S 9/27/2018 $1,667
Tony S 10/27/2018 $1,667
Tony S 11/26/2018 $1,667
Tony S 12/26/2018 $1,667
Tony S 1/25/2019 $1,667
Tony S 2/24/2019 $1,667
Tony S 3/26/2019 $1,667
Tony S 4/25/2019 $1,667
Tony S 5/25/2019 $1,667
Tony S 6/24/2019 $1,667
Tony S 7/24/2019 $1,667
Tony S 8/23/2019 $1,667
Tony S 9/22/2019 $1,667
Tony S 10/22/2019 $1,667
Bruce B 1/30/2019 $1,667
Bruce B 3/1/2019 $1,667
Bruce B 3/31/2019 $1,667
Bruce B 4/30/2019 $1,667
Bruce B 5/30/2019 $1,667
Bruce B 6/29/2019 $1,667
Bruce B 7/29/2019 $1,667
Bruce B 8/28/2019 $1,667
Bruce B 9/27/2019 $1,667
Bruce B 10/27/2019 $1,667

The formula seems easy. Give the number of months (periods) in where.... sum of Payroll = total Fees + (Avg Balance *0.9*0.00246 * Number of months)

the average balance Im multiplying for .9 since the company can only invest 905 of the avg balance, and multiplying by 0.00246 since its the monthly interest rate for the company (and that is the actual income the account brings to the company) times the number of months the account has been open.

the last part is important, because if an account is closed, it no longer generates income.

I really dont even know where to start, any  help would be appreciated.

[Anonymous]

Forgot to mention, the payroll is the same for everyone just for the sake of this example.

[dax]

Hi garzamlan,

Yes, I could get your sample, but if possible, could you please inform me your expected output? Then I will help you more correctly. In addition, if possible, could you please explain below things to me:

1. what is the function of payroll table?
2. if the account close date is blank, do you want to use today()  as close date? If the close date is over than terminal date, will you want to calculate month based terminal date?

Best Regards,
Zoe Zhi

[Anonymous]

Thank you for the Follow-up.

Let me answer the questions.

Expected Output = Average Months of Break even, so if the measurements calculate 8, 9 and 10 months for breakeven for the 3 salesmen of the example, then the expected number is 9.

1. The function of the Payroll table is to know the amount of money paid to each salesman.

       So to better explain, if Peter P earns X amount per month and generates Y amount per month, in how many months X=Y?

2. If the account close date is blank, means is active, and it will keep generating income, I didn't do the math, so possibly in the example data the breakeven point is reached before "today", or maybe not.

Any further questions, please let me know! I'm still trying logics, but no luck!!