Measure to calculate the consolidated incentive below 100

[Ramees_123]

Hi, Please find the below table data I have for incentives. 

The company does not payout any incentive if the achieved value is below 100. In that case they will add it to the next month incentive until 100 is being achieved. Once 100 is achieved, the payout will be done to the employee.

In the below table the incentive achieved by Person1 in Jan 2021 is just 30, so the payout is 0. But in next month the achieved value is 70 and so the payout is 100 which is the sum of 30 + 70. In March 2021, the achieved 130 and there is not pending amount from previous month too. so the payout will be 130 only.

How to create this incentive payout measure field ? I need only measure field since the data is connected live to cube and calculated column is not possible.

[v-rongtiep-msft · Accepted solution]

You could refer this measure.

Measure = 
var Cumulative = CALCULATE(SUM('Table'[Incentive Achieved ]),FILTER(ALLSELECTED('Table'),'Table'[resource ID] =SELECTEDVALUE('Table'[resource ID])&& 'Table'[Month]<=MAX('Table'[Month]))) 
var last = CALCULATE(SUM('Table'[Incentive Achieved ]),FILTER(ALLSELECTED('Table'),'Table'[resource ID]=SELECTEDVALUE('Table'[resource ID]) && 'Table'[Month]<=EDATE(MAX('Table'[Month]),-1)))+0  
return IF([Cumulative value]>=100&&[last values]<100,[Cumulative value],IF([Cumulative value]>=100&&SELECTEDVALUE('Table'[Incentive Achieved ])>=100,SELECTEDVALUE('Table'[Incentive Achieved ]),0))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Polly

[Ramees_123]

@v-rongtiep-msft It is excellent. Thanks a lot. Saved my day. !!!

[lbendlin]

When does the clock start? at the beginning of the year?  After they reached 100 points will they get a payout in each of the following months?

[Ramees_123]

@lbendlin After reaching 100 points they will get that payout in upcoming month and then again the points go to 0. Clock starts by the beginning of the year. But the December pending payout will be taken to January.

[lbendlin]

That seems to be inconsistent wit your sample data.  Please post a larger sample size, including year cutover for individual resoures.

Please keep in mind that Power BI is a reporting tool. It is not a business process management tool.