Calculate bonus amount for retroactive months.

[Rai_Lomarques]

Hello friends. In Power BI, I need to create a DAX measure that calculates the recovery bonus value, as shown in the attached illustrative table.

I have a table called "fSales" containing all sales data. Another table called "fGoals" contains all target data by salesperson, month, and row.

Note that in month 01/2025, the monthly target achievement is 100%, allowing for the payment of a monthly bonus.

In month 02/2025, the monthly target achievement is 94%, not allowing for the payment of a monthly bonus. In this case, month 02/2025 can be recovered in a future month when the YTD achievement reaches at least 100%.

In month 03/2025, the monthly target achievement is 103%, allowing for the payment of a monthly bonus. In this case, the month of February 2025, which did not receive a bonus, can be recovered, as the YTD reached at least 100%.

In 04/2025, the monthly target was achieved at 112%, allowing for the payment of a monthly bonus. There are no amounts to be recovered, as the month of February was already recovered in 03/2025.

In 05/2025, the monthly target was achieved at 91%, not allowing for the payment of a monthly bonus. However, the YTD is at 104%, in which case the bonus advance is paid. The month of 05/2025 does not need to be recovered, because although there was no monthly bonus payment, there was an advance payment.

In 06/2025, the monthly target was achieved at 98%, not allowing for the payment of a monthly bonus. But the YTD is at 104%, in which case the bonus advance is paid. The month of June 2025 does not need to be recovered, because although there was no monthly bonus payment, there was an advance payment.

In 07/2025, the monthly target is 100% achieved, allowing for the payment of the monthly bonus. Everything is correct, there are no amounts to be recovered.

In 08/2025, the monthly target is 90% achieved, not allowing for the payment of the monthly bonus. The YTD is at 97% and does not allow for the payment of an advance. In this case, August 2025 can be recovered in a future month when the YTD reaches at least 100%.

In 09/2025, the monthly target is 107% achieved, allowing for the payment of the monthly bonus. In this case, the month of August 2025, which did not receive a bonus, can be recovered because the YTD (Year-to-Date) achievement reached at least 100%, that is, it reached 101%.

In 10/2025, the monthly target achievement is 103%, allowing for the payment of a monthly bonus. There are no recoverable amounts to be paid.

In 11/2025, the monthly target achievement is 101%, allowing for the payment of a monthly bonus. There are no recoverable amounts to be paid.

In 12/2025, the monthly target achievement is 100%, allowing for the payment of a monthly bonus. There are no recoverable amounts to be paid.

[MohdZaid_ · Accepted solution]

Hey @Rai_Lomarques  , 

Retroactive Months.pbix

Please download and review the PBIX file from the provided URL. Kindly ignore the additional Research and Development DAX measures.

Create a customized table and apply the same logic to your own dataset.
Measure Name - "Recovery Bonus (Final Recovery Amount) Fianally"

If you need anything else then let me know.

[techies]

Hi @Rai_Lomarques have tested the logic using sample datasets, and it is working as per the defined conditions. 

• If the monthly target is greater than or equal to 100%, a monthly bonus will be paid.

• If the monthly target is below 100% but the year-to-date (YTD) target is greater than or equal to 100%, an advance bonus will be paid.

• If neither condition is met, no monthly or advance bonus will be paid.

• If the YTD target is greater than or equal to 100% and the current month’s target is greater than or equal to 100%, previous months in which no monthly or advance bonus was paid will be reviewed, and a recovery bonus will be issued accordingly.

Please review the attached file.

[Rai_Lomarques]

Hi @techies  thanks for your help.

Your solution is not working correctly for me.
The measure is only checking the last month, but it may happen that there is a month older than the last month that needs to be retrieved.

See the example in the image below:

 

[techies]

Hi @Rai_Lomarques thanks for the clear explanation, it works now. Sharing the pbix file for reference.

[MohdZaid_]

Hey @Rai_Lomarques , 

When do we pay Recovery Bonus?

1. A month must be recovered when:
2. Monthly Goal % < 100%
3. No Advance Bonus was paid
4. Later, in a month where:
   • Monthly Goal % ≥ 100%
   • YTD Goal % ≥ 100%
5. The recovery can only happen once per failed month.

So essentially we must:

• Identify failed months eligible for recovery
• Track if they were already recovered
• When YTD reaches ≥ 100% and current month qualifies, release pending months

We need to:

1. Build a virtual table of months up to current context
2. Identify:
   • Months where Monthly % < 100
   • No Advance was paid
   • Not yet recovered
3. When current month:
   • Monthly ≥ 100
   • YTD ≥ 100
     → Pay all pending months

Assumptions:

'fGoals' contains:

• [Month]
• [Reach Goal Month %]
• [Reach YTD Target %]
• [Monthly Bonus Value]
• [Advance Bonus Amount]

Here is the DAX measure:

Recovery Bonus Value =
VAR CurrentMonth = MAX('fGoals'[Month])
VAR CurrentMonthlyPct = MAX('fGoals'[Reach Goal Month %])
VAR CurrentYTDPct = MAX('fGoals'[Reach YTD Target %])

-- Check if current month allows recovery trigger
VAR CanTriggerRecovery =
CurrentMonthlyPct >= 1 &&
CurrentYTDPct >= 1

-- All previous months eligible for recovery
VAR PendingMonths =
FILTER(
ALL('fGoals'),
'fGoals'[Month] < CurrentMonth &&
'fGoals'[Reach Goal Month %] < 1 &&
'fGoals'[Advance Bonus Amount] = BLANK()
)

-- Remove months already recovered
VAR AlreadyRecoveredMonths =
CALCULATETABLE(
VALUES('fGoals'[Month]),
FILTER(
ALL('fGoals'),
'fGoals'[Recovery Bonus Value] <> BLANK()
)
)

VAR FinalPendingMonths =
EXCEPT(
SELECTCOLUMNS(PendingMonths, "Month", 'fGoals'[Month]),
AlreadyRecoveredMonths
)

VAR RecoveryAmount =
SUMX(
FinalPendingMonths,
CALCULATE(MAX('fGoals'[Monthly Bonus Value]))
)

RETURN
IF(
CanTriggerRecovery,
RecoveryAmount,
BLANK()
)

Important

If [Recovery Bonus Value] is a calculated column instead of a measure, we need a slightly different approach (using EARLIER or indexed logic).

If your model includes multiple salespeople, we must wrap everything inside:

ALLEXCEPT('fGoals', 'fGoals'[Salesperson])

to keep calculations isolated per salesperson.

If it solved your issue, feel free to mark it as the solution so others can benefit too.

Thanks for being part of the community.

[Rai_Lomarques]

If the monthly target is greater than or equal to 100%, then a bonus will be paid.

If the monthly target is not met, but the year-to-date (YTD) target is greater than or equal to 100%, then an advance bonus will be paid. Otherwise, there will be no monthly bonus or advance bonus payment.

If the year-to-date (YTD) target is greater than or equal to 100% AND the current month's target is greater than or equal to 100%, then the retroactive months in which no monthly bonus or advance bonus was paid will be analyzed, and a recovery bonus will be paid. Caution is needed because if the recovery bonus has already been paid, it should not be paid again.

[MohdZaid_]

Hey @Rai_Lomarques  , 

Important Clarification

In the shared photo, July (07/2025) shows a recovery bonus.

However, based on the business logic:

In 07/2025, the monthly target is 100% achieved, allowing for the payment of the monthly bonus. Everything is correct, there are no amounts to be recovered.

There is no pending failed month before July (May and June had Advance Bonus paid), so July should NOT have a recovery bonus.

The photo contains an error for July.

Correct recovery months should be:

03/2025 (recovers February)
09/2025 (recovers August)

Here is the DAX :

Recovery Bonus (Final Recovery Amount) Fianally = 
VAR CurrentMonth = MAX(fGoals[MonthYear])

-- Is current month eligible to recover?
VAR IsEligibleMonth =
    MAX(fGoals[Reach Goal Month (%)]) >= 100 &&
    MAX(fGoals[Reach YTD Target (%)]) >= 100

-- Find previous months that failed AND had no advance
VAR FailedMonths =
    FILTER (
        ALL ( fGoals ),
        fGoals[MonthYear] < CurrentMonth &&
        fGoals[Reach Goal Month (%)] < 100 &&
        CALCULATE ( [Advance Bonus Value C] ) = BLANK ()
    )

-- From those failed months,
-- count only those whose first eligible recovery month is THIS month
VAR RecoverNow =
    COUNTROWS (
        FILTER (
            FailedMonths,
            VAR FailedMonthDate = fGoals[MonthYear]

            VAR FirstEligibleMonth =
                CALCULATE (
                    MIN ( fGoals[MonthYear] ),
                    FILTER (
                        ALL ( fGoals ),
                        fGoals[MonthYear] > FailedMonthDate &&
                        fGoals[Reach Goal Month (%)] >= 100 &&
                        fGoals[Reach YTD Target (%)] >= 100
                    )
                )
            RETURN
                FirstEligibleMonth = CurrentMonth
        )
    )

var RETURN1 = 
IF (
    IsEligibleMonth,
    RecoverNow * 2700,
    BLANK ()
)
RETURN RETURN1

In your code create Summarized table then you can use this 

If you find this solution helpful and it resolves your issue, please consider marking it as the accepted solution and giving it a kudos 👍

It took significant effort to structure the logic correctly, and your support is truly appreciated.

[Rai_Lomarques]

Hi @MohdZaid_  can you share th pbix file?

[MohdZaid_ · Accepted solution]

Hey @Rai_Lomarques  , 

Retroactive Months.pbix

Please download and review the PBIX file from the provided URL. Kindly ignore the additional Research and Development DAX measures.

Create a customized table and apply the same logic to your own dataset.
Measure Name - "Recovery Bonus (Final Recovery Amount) Fianally"

If you need anything else then let me know.

[MohdZaid_]

Hey @Rai_Lomarques  , 

Have you check ?
its working or you need anything else ?

[Rai_Lomarques]

Hello,
Yes it is working, but you have created new columns in the goals table. My data model is very large and it is very cumbersome to create new additional columns. I was hoping to be able to create the DAX measure without having to create new columns in the data model.

[MohdZaid_ · Accepted solution]

Hey @Rai_Lomarques  , 


I have just created Monthly Achivement % and Yearly Achivement %.
Rest you can delete and this will work.

you may not achive this if Monthly and Yearly Achivement % will be in DAX Measure.

[v-nmadadi-msft]

Hi @Rai_Lomarques 

I wanted to check if you had the opportunity to review the information provided by @MohdZaid_ . Please feel free to contact us if you have any further questions.

Thank you.

[v-nmadadi-msft]

Hi @Rai_Lomarques 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[Irwan]

hello @Rai_Lomarques 

if i am not misunderstood, i would assume like this

- if the monthly goal is not reached then no monthly bonus

- if YTD goal is not reached then no advance bonus in corresponding month

- if both monthly goal and YTD target achived AND no recover bonus on previous month then recover bonus is given.

- recover bonus is not repeated next month if next consecutive month has achived monthly goal and YTD target.

where do monthly goal and YTD target value come from? is it a inputed value or a measure from another source?

Thank you.

[Rai_Lomarques]

Here is the PBIX example
https://1drv.ms/u/c/1053487a0025ef71/IQCUS3BmlGEfRp2pdwXjzfS1AZINt_qv29MOh6MOqof3u5Q?e=1Gsis3

[Rai_Lomarques]

1. If the monthly target is greater than or equal to 100%, then a bonus will be paid.
2. If the monthly target is not met but the year-to-date (YTD) target is greater than or equal to 100%, then an advance bonus will be paid. Otherwise, there will be no monthly bonus or advance bonus payment.
3. If the year-to-date (YTD) target is greater than or equal to 100% AND the current month's target is greater than or equal to 100%, then the retroactive months in which no monthly bonus or advance bonus was paid will be analyzed, and a recovery bonus will be paid.