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

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 👍

     

4 Replies

  • 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)

     

     

     

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