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
l3montrees's avatar
l3montrees
Frequent Visitor
5 years ago

So I did manage to make it work in Power BI by using the DAX code above but I'd still appreciate some help in order to make it work in Power Pivot. My problem though is slightly more complex as there might be cases that the initial salary / the base for each respective month, might not be the same across all  the months as i might decide to promote a staff member. in that  case the solution cannot work as if the timing of the increase is before the new salary calc then what the code is doing is compounding the new initial salary amount while it should not do it and for this month and onwards the salary should be the same unless at  this point or later on there is a 2nd % increase in salary. so the question is how do we make the compounding reset back to 1 when the initial salary changes?

l3montrees's avatar
l3montrees
Frequent Visitor
5 years ago

hmm..experimented a little bit more and manage to make it work with the SELECTEDVALUE() and definition of respective variables so that my criteria are met. Now, the question remains, how can we do the same in Power Pivot where the SELECTEDVALUE is not available ??