Measures for incentive calculation based on margin after allocating annual fixed costs

[MarieBell]

I am a new Power BI user and trying to translate Excel spreadsheets into Power BI reports.

We have sales, purchase and margin data for a salesperson that we use to calculate an monthly incentive amount after deducting 1/12 of some annual costs.

Each quarter 75% of the total incentive is paid to the salesperson with the balance after the year end.

There is a salary table for the employee with start and end dates that provides the data for the annual costs, which will be added to when any of the costs change.

All the calculations are easy to do in Excel, but I can't find answers that I can understand enough to apply to my scenario and am stuck on point 1 below let alone moving onto the more complicated points afterwards. 

This is what I need to be able to do:

1. Total Costs - Pick up the relevant row in the Salary table that applies to the month and calculate the costs for the month as 1/12 of Total Costs (annual fixed costs of their salary, vehicle costs, top up allowance).  Show correct grand totals.
2. Calculate the Net Margin = Margin from transaction data (Table called Data) less Total Costs (from 1 above). Show correct grand totals.
3. Net Margin YTD = from 2 above using Fiscal YTD figures
4. Sales Incentive = 15% of net margin for month (2 above). Show correct grand totals.
5. Sales Incentive YTD = from 4 above using Fiscal YTD figures
6. Purchases Margin = Purchases total from transaction data multiplied by the markup table % for that Category. Show correct grand totals.
7. Purchase Incentive = 1.75% of purchases margin for month (6 above). Show correct grand totals.
8. Purchase Incentive YTD = from 7 above using Fiscal YTD figures
9. Total Incentive YTD = 5 + 8 above
10. Payment 75% each quarter date plus balance

I’ve attached the links below to the spreadsheet I am trying to replicate and the Power BI where I have started.  This report will be handed over to someone with no PowerQuery or Power Bi knowledge to refresh and update data so user interactions need to be as easy as possible.

I have tried various calcs to split annual costs into monthly, but either can't get the code to work or it breaks when I add another row on the salary table.  I am wondering if it is too complex for me to handle and too easily broken so should be left in Excel, especially as I am stuck on point 1 of 10!

Incentive calculation.xlsx

Incentive Scheme Monthly Report.pbix

Any help to point me in the right direction would be much appreciated. 

Thanks

Marie

[MarieBell]

Sorry for the delay, I've been offline for 4 weeks.  These links both relate to allocating by the day so it will vary depending on the number of days in the month.  My figures need to remain static for each month irrelevant on the number of days in it

[amitchandak]

@MarieBell , Try if these approaches can help

powerbi Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...

PowerBI Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...