Forum Discussion

Sandeep_Warrier's avatar
Sandeep_Warrier
Regular Visitor
3 years ago
Solved

Calculate % increase over time

Hi All,   I am looking to calculate % increase of salary over the past 2 years. The data might not have data for all two years, and there can be multiple changes in salary in a year (promotion, ad-...
Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    yes, if is not a good choice with calculate. try allexcept instead of all. 

     

    for example

    Percent Increase =
    VAR NewAvgSal = CALCULATE(
    AVERAGE(Compensation[Final CTC]),
    ALLEXCEPT(Compensation, Compensation[Year (FY Year Start)]),
    Compensation[Year (FY Year Start)] = MAX(Compensation[Year (FY Year Start)])
    )
    VAR MinYear = CALCULATE(
    MIN(Compensation[Year (FY Year Start)]),
    ALLEXCEPT(Compensation, Compensation[Year (FY Year Start)])
    )
    VAR PrevYear = EDATE(MAX(Compensation[Year (FY Year Start)]), -24)
    VAR AvgSal2YearsAgo = CALCULATE(
    AVERAGE(Compensation[Final CTC]),
    ALLEXCEPT(Compensation, Compensation[Year (FY Year Start)]),
    Compensation[Year (FY Year Start)] = PrevYear
    )
    VAR OldAvgSal = IF(
    ISBLANK(AvgSal2YearsAgo),
    CALCULATE(
    AVERAGE(Compensation[Final CTC]),
    ALLEXCEPT(Compensation, Compensation[Year (FY Year Start)]),
    Compensation[Year (FY Year Start)] = MinYear
    ),
    AvgSal2YearsAgo
    )
    RETURN DIVIDE(NewAvgSal - OldAvgSal, OldAvgSal)

     

    you need to adjust further. 

     

    if post helped you in any way, hit 👍

     

rubayatyasmin's avatar
rubayatyasmin
Community Champion
3 years ago

Hi, Sandeep_Warrier 

 

The problem you're having might stem from the use of the IF function. You're trying to use it to conditionally set the filter context for the CALCULATE function, but it's not clear which column the IF function should return, which is why you're getting this error.

What you want to do instead is to change the structure of your IF condition to return a table, rather than trying to return a value from a calculation directly.

Here is a revised version of your code that should work:

 

Percent Increase =
var NewAvgSal = CALCULATE(AVERAGE(Compensation[Final CTC]),ALL(Compensation),Compensation[Year (FY Year Start)] = MAX(Compensation[Year (FY Year Start)]))
var MinYear = CALCULATE(MIN(Compensation[Year (FY Year Start)]), ALL(Compensation))
var PrevYear = EDATE(MAX(Compensation[Year (FY Year Start)]), -24)
var OldAvgSal =
CALCULATE(AVERAGE(Compensation[Final CTC]),
ALL(Compensation),
Compensation[Year (FY Year Start)] =
IF(
ISBLANK(CALCULATE(AVERAGE(Compensation[Final CTC]), ALL(Compensation), Compensation[Year (FY Year Start)] = PrevYear)),
MinYear,
PrevYear
)
)
RETURN DIVIDE(NewAvgSal - OldAvgSal, OldAvgSal)