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.