Calculate % increase over time

[Sandeep_Warrier]

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-hoc change etc.). I was looking at a condition where if the date two years ago, then use the min date, else use the date of two years ago.

I have this DAX, but getting an error - "The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

Here is the DAX - 

Percent Increase =

var NewAvgSal = CALCULATE(AVERAGE(Compensation[Final CTC]),MAX(Compensation[Year (FY Year Start)]))

var oldAvgSal =

IF(

CALCULATE(AVERAGE(Compensation[Final CTC]),DATEADD(Compensation[Year (FY Year Start)],-24,MONTH)) = BLANK(),CALCULATE(AVERAGE(Compensation[Final CTC]),MIN(Compensation[Year (FY Year Start)])),CALCULATE(AVERAGE(Compensation[Final CTC]),DATEADD(Compensation[Year (FY Year Start)],-24,MONTH)))

Return

DIVIDE(NewAvgSal-oldAvgSal,oldAvgSal)

Appreciate some insights on what I am doing wrong here.

Regards,

Sandeep

[Sandeep_Warrier]

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 · Accepted solution]

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]

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.

[rubayatyasmin]

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)