Compound Salary Increase

[AndrewI]

Hello, I am attempting to write a Salary Forecast Report.

The simplified Data Table contains the following Fields

EmployeeID         FYMonth       FTESalary             %Increase        |    Forecasted Salary 

      1                           1               5000                           0                            5000

      1                           2               5000                           0.03                       5150

      1                           3               5000                           0                            5150

      1                           4               5000                           0.02                       5253

.......

      2                           1               1000                           0                           1000

      2                           2               1000                          0.03                       1030

      2                           3               1000                           0                           1030

      2                           4               1000                           0.02                      1050.60

 ......

I need to calculate the compounded Forecasted Salary  above.

Any ideas on how to achieve this would be appreciated.                     

[AndrewI]

Thanks @parry2k  @Anonymous ,

After testing, natelpeterson's method gave the anticipated results.

I'm not sure why the second method is close but not correct.

[l3montrees]

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]

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

[l3montrees]

hi @AndrewI 

any chance you could share a sample of this solution so that to uunderstand its logic and be able to apply in a similar problem? also, did you try to do the same in PQ/ Power Pivot in Excel?

[Anonymous · Accepted solution]

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]

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!

[parry2k]

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