Forum Discussion

Rai_Lomarques's avatar
Rai_Lomarques
Icon for Helper II rankHelper II
6 months ago
Solved

DAX for calculating retroactive values

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: ...
  • v-prasare's avatar
    v-prasare
    6 months ago

    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

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 months ago

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's avatar
    Rai_Lomarques
    Icon for Helper II rankHelper II
    6 months ago

    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%.