Forum Discussion

AndrewI's avatar
AndrewI
Frequent Visitor
7 years ago
Solved

Compound Salary Increase

Hello, I am attempting to write a Salary Forecast Report.   The simplified Data Table contains the following Fields EmployeeID         FYMonth       FTESalary             %Increase        |    For...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi AndrewI ,

    The following is derived from this blog:

     

    Compounding =
    var Empl = SELECTEDVALUE(RatesMonth[EmpId])
    return
    EXP(
    SUMX(
    FILTER(
    ALL(RatesMonth),
    RatesMonth[Month]<=MAX(RatesMonth[Month]) && RatesMonth[EmpId] = Empl
    ),
    LN(1 + RatesMonth[Growth])
    )
    )
     
     
    To get the money amount, you can multiply the Compounding by the money amount.
     
    Hope This Helps
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi AndrewI ,

The following is derived from this blog:

 

Compounding =
var Empl = SELECTEDVALUE(RatesMonth[EmpId])
return
EXP(
SUMX(
FILTER(
ALL(RatesMonth),
RatesMonth[Month]<=MAX(RatesMonth[Month]) && RatesMonth[EmpId] = Empl
),
LN(1 + RatesMonth[Growth])
)
)
 
 
To get the money amount, you can multiply the Compounding by the money amount.
 
Hope This Helps
  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    AndrewI I didn't tested the solution but let's try this.

     

    first add measure to calculate cummulative % increase

     

     

    Increase **bleep** = 

VAR x =  CALCULATE( SUM( Table6[Increase] ),FILTER( ALLEXCEPT( Table6, Table6[Employee] ), Table6[Month] <= MAX( Table6[Month] ) ) )
VAR y =  CALCULATE( SUM( Table6[Increase] ) )
VAR z =  CALCULATE( SUM( Table6[Increase] ),FILTER( ALLEXCEPT( Table6, Table6[Employee] ), Table6[Month]  < MAX( Table6[Month] ) ) )

RETURN  x + (z * y)

    now multiply this with salary to get forecast salary

     

    Forecast Salary = 
VAR s = SUM( Table6[Salary] ) 
RETURN (s*[Increase **bleep**]) + s

    and it should work.

     

  • l3montrees's avatar
    l3montrees
    Frequent Visitor
    5 years ago

    I am having a similar challenge but in my case the data is on Power Query in Excel where the SELECTEDVALUE() is not supported. Can you please tell me on how to do the same thing in Excel / PQ with another function and also explain the steps?

     

    thank you in advance!