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 👍

     

Sandeep_Warrier's avatar
Sandeep_Warrier
Regular Visitor
3 years ago

Hi rubayatyasmin  -- thanks for your response!

 

On trying this, another error comes up - A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

I had another question -- will the use of ALL in the variables not negate the effect of any slicers I might have?

 

Regards,

Sandeep

rubayatyasmin's avatar
rubayatyasmin
Community Champion
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 👍

 

  • Sandeep_Warrier's avatar
    Sandeep_Warrier
    Regular Visitor
    3 years ago

    Thanks for this! Using the additional variable worked. I did not use ALL or ALLEXCEPT. Will experiment on the data and see if it is needed.