Help needed to create Sales over Target Measure

[keremoyilmaz]

Dear Community,

I have a model with tables of sales, targets and custom date. 

I need to calculate sales achievement with rules as explained below and with the example table which is showing all the possible scenarios. 

• If sales / target ratio is less than 80%, "target is not achieved" No remaining for the next quarter. 
• If sales / target ratio is between 80% and 100%, "target achieved with remaining"
  • Remaining target is added to next quarter.
  • In the next quarter, firstly remaining sales from previous quarter has to be closed and then calculation has to be made for that quarter
• If sales / target ratio is 100%, "target achieved"
• If sales / target ratio is above 100%, "target over-achieved", over achieved amount is added to next quarter for the calculation of ratio.
• All above works within quarter 1 to quarter 4 which means
  • Quarter 1 resets everything, in other words no remaining or over achievement from quarter 4 previous year to quarter 1 this year.

Moreover, I will have a single select slicer with quarters and within a matrix visual, I need to show the target, sales and Achievement % columns.

I hope I could clearly explain the logic of this rule and what I need. 

Thank you very much in advance for your time and for your help

	Quarter 1	Quarter 2	Quarter 3	Quarter 4
Target Quarter	200	200	200	200
Sales Quarter	150	160	300	140
Achievement % (after completion of remaining from previous quarter)	75%	80%	130%	100%
Achievement Status (min 80%)	Not achieved<80%	80%<Achieved with remaining<100%	100%<Over-Achieved	Achieved = 100%
sales minus target (to be added to next quarter)	0	-40	60	0