DAX for calculating retroactive values

[Rai_Lomarques]

Hello fellow Power BI ninjas!

I'm having a lot of trouble creating a DAX measure that allows me to calculate bonus payments for retroactive months.

Downlaod PBIX here.

The logic is as follows:

The salesperson can earn the monthly bonus if they reach 100% of their monthly target. I call this bonus the "Monthly Bonus".

If the salesperson does not reach 100% of the monthly target, they will not be entitled to the "Monthly Bonus". However, if the year-to-date (YTD) total is >= 100%, they may receive an advance on the bonus to compensate for not reaching the monthly target. I call this bonus the "Bonus Advance".

If the salesperson does not reach 100% of the monthly target, they will not be entitled to the "Monthly Bonus", and also if the year-to-date (YTD) total is less than 100%, the salesperson will not receive an advance bonus, meaning the salesperson will not receive any type of bonus. In this case, that month is marked as "Eligible for recovery," meaning the seller can recover the bonus for that month in a future month when the YTD reaches at least 100%.

A month "Eligible for recovery" can only be recovered once. Once recovered, that month cannot be recovered again.

The image below better illustrates the payment logic:

 

[v-prasare]

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare · Accepted solution]

Hi @Rai_Lomarques,

Could you please try using below DAX queries and let me know if it helps resolev your query.

Monthly Bonus
==============
Monthly Bonus =
VAR MonthlyReach = MAX('Bonus Data'[Monthly Reach %])
VAR BonusValue   = 2700

RETURN
    IF(MonthlyReach >= 1, BonusValue, 0)

Advance Bonus
=============
Pays when monthly is below target but the running YTD compensates (≥ 100%).

Advance Bonus =
VAR MonthlyReach = MAX('Bonus Data'[Monthly Reach %])
VAR YTDReach     = MAX('Bonus Data'[YTD Reach %])
VAR BonusValue   = 2700

RETURN
    IF(
        MonthlyReach < 1 && YTDReach >= 1,
        BonusValue,
        0
    )

Recovery Bonus
==============

Recovery Bonus =
VAR CurrentDate = MAX('Bonus Data'[Month])
VAR CurrentYTD  = MAX('Bonus Data'[YTD Reach %])
VAR BonusValue  = 2700

-- All months before current with no bonus (monthly < 100% AND YTD < 100%)
VAR EligibleMonths =
    FILTER(
        ALL('Bonus Data'),
        'Bonus Data'[Month] < CurrentDate
&& 'Bonus Data'[Monthly Reach %] < 1
&& 'Bonus Data'[YTD Reach %] < 1
    )

-- For each eligible month, pay it only if the current month is
-- the FIRST month after it where YTD >= 100%
VAR RecoveredThisPeriod =
    SUMX(
        EligibleMonths,
        VAR EligibleMonth = 'Bonus Data'[Month]

 -- Earliest future month with YTD >= 100%
        VAR FirstRecoveryMonth =
            MINX(
                FILTER(
                    ALL('Bonus Data'),
                    'Bonus Data'[Month] > EligibleMonth
&& 'Bonus Data'[YTD Reach %] >= 1
                ),
                'Bonus Data'[Month]
            )

 -- Count this eligible month only if current = first recovery month
        RETURN
            IF(FirstRecoveryMonth = CurrentDate, BonusValue, 0)
    )

RETURN
    IF(CurrentYTD >= 1, RecoveredThisPeriod, 0)

Thanks,

Prashanth

[Rai_Lomarques]

It seems to be working. Thank you very much, I'll do some more tests.

[lbendlin]

DAX has no concept of mutable variables and does not support conditional aggregation.  Only List.Accumulate in Power Query supports that.

You should not use TOTALYTD if your data model does not have a valid calendar table.

However, if the year-to-date (YTD) total is >= 100%, they may receive an advance on the bonus to compensate for not reaching the monthly target. I call this bonus the "Bonus Advance".

That means that the Bonus Advance can be taken away again if the performance decreases as the year goes on.  How do you want to visualize that?  You would need to show the bonus values for each prior month over time as the year progresses. Sounds like a recipe for making your sales people angry.

In your screenshot, why is there no bonus in month 12?

Still not clear on the mechanics of the advance bonus.

[Rai_Lomarques]

I intend to visualize exactly as in the example image I attached in the main thread.
In month 12/2025 is no bonus payment because the target for the month was not reached, that is, it is less than 100%. And there is also no payment of an advance bonus or recovery bonus because the YTD is less than 100%.