Measure to Calculate the Pending Amounts

[Ramees_123]

Hi, Please find the below employees incentive table. We need to create a measure for the field Final Payout.

Company does not release if the incentive achieved is < 100, instead it will add it to the next month payout.

In the below example Empoyee 1024 achieved incentive for Jan 2021, Feb 2021 and April 2021. He didn't achieve any for Mar 2021 due to some reasons.

For Jan he achieved only 30, so final payout is 0. For Feb he achieved only 35 and if we add up the previous incentive then we goet only 65, so final payout is 0. For March, he didn't have any entry. For April he achived 40 and when we sum up previous pendings of 30 and 35, then the toal final payout is 105 and he receives it since it is greater than 100.

How to create this type of measure. Please help. This table will have records for multiple employee IDs and months and years.

Employee ID	Month - Year	Incentive Achieved	Final Payout
1024	Jan-21	30	0
1024	Feb-21	35	0
1024	Apr-21	40	105

[Ashish_Mathur]

Hi,

This is not as easy as you have made it out in the 3 rows example.  For employee 1024, please take a more comprehensive example with multiple Final payouts during the year to fortify the logic and understanding.

[v-chenwuz-msft · Accepted solution]

Hi Ramees_123,

Try this measure

Final Payout measure = 
VAR IF_gr100=CALCULATE(SUM('TABLE'[Incentive Achieved]),
    FILTER(all('TABLE'), 
        'TABLE'[Month - Year]<=MAX('TABLE'[Month - Year])&& 'TABLE'[Employee ID]=MAX('TABLE'[Employee ID])
    )
)
return
IF(IF_gr100>=100,IF_gr100,0)

 

Best Regards

Community Support Team _ chenwu zhu

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