Automate Salary Increase calculations for HR Budget

[l3montrees]

Need some assistance with a data issue...I work on a 12month HR budget where the monthly initial salary of each monthly might be different due to a upcoming promotion and salary change. the percent increase might be the same for all staff or might be different based on their rank. Timing of the increase can be anytime within the budget year but it will be consistent for the group ie in the case we apply an X% increase for the year this will happen the same month for everyone. 
So I have 12 rows-months for each staff member with their salary amount each month. Then I want to be able to apply a % growth assumption that it might be applied at January but potentially any month within the year. So for example, John has an initial salary of 3000 and I intend to adjust his salary by 10% in May this year. So, I need a smart calculation that will understand the timing for the salary growth ie 1.10% and also be flexible to start paying escalating the correct salary amount. The latter is important as it is not fixed that John’s budgeted initial salary will be the same all year long. I might also have another assumption that I will promote him at March and his initial salary will go to 4000. For Jan and Feb his Salary should be 3000, then on March till April it will be 4000 due to the promotion and on May he will also get the 10% increase that will be applied till the end of year ie his May till December salary should be 4400.
I have managed to automate in Power Query the promotion salary adjusting but I struggle to find an easy way to adjust the % growth on whatever salary at a given point in time.

I hope my description is good and there are some useful ideas !😅

[Jimmy801]

Hello @l3montrees 

check this solution. First table is your employee-table the second the increases. To the first table a new column is added that applies the increases in the second table. If the Column "Person" is empty, to all employee is added the increase, otherwise only to a specific person.

let
    Employee = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddC5CcAwEETRXjYWZnT4Cj1tCPXfhpUZjD5ssPxsXu/xRArlbV7RfKukGOnrBXqF3qDv0A/oJ/QL+r3uWdBhb/7vNfgYfAw+Bh+Dj8HH4GPwMfgYfAw+XvmMFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Date = _t, Sallary = _t]),
    ChType = Table.TransformColumnTypes(Employee,{{"Person", type text}, {"Date", type date, "de-DE"}, {"Sallary", Int64.Type}}),
    Increase = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMjDUMzDVMzIAMXUMTJVidaKVHCHihgbI4rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Date = _t, Increase = _t]),
    ChType1 = Table.TransformColumnTypes(Increase,{{"Person", type text}, {"Date", type date, "de-DE"}, {"Increase",Percentage.Type}}),
    CreateColumn = List.Generate
    (
        ()=> [Person =ChType[Person]{0}, Counter= 0, Sallary= ChType[Sallary]{0}, NewSallary = if Table.IsEmpty(Table.SelectRows(ChType1, each ([Person]=ChType[Person]{0} and [Date]=ChType[Date]{0}) or ([Person]="" and [Date]=ChType[Date]{0}) )) then ChType[Sallary]{0} else ChType[Sallary]{0}*(Table.SelectRows(ChType1,([Person]=ChType[Person]{0} and [Date]=ChType[Date]{0}) or ([Person]="" and [Date]=ChType[Date]{0}))[Increase]{0}+1) ],
        each [Counter]<= Table.RowCount(ChType)-1,
        (rec)=> [Person =ChType[Person]{rec[Counter]+1},  Counter= rec[Counter]+1, Sallary= ChType[Sallary]{rec[Counter]+1}, NewSallary = if rec[Person]<> ChType[Person]{rec[Counter]+1} then  ChType[Sallary]{rec[Counter]+1} else if Table.IsEmpty(Table.SelectRows(ChType1, each ([Person]=ChType[Person]{rec[Counter]+1} and [Date]=ChType[Date]{rec[Counter]+1}) or ([Person]="" and [Date]=ChType[Date]{rec[Counter]+1}))) then rec[NewSallary] else rec[NewSallary] *(Table.SelectRows(ChType1, each ([Person]=ChType[Person]{rec[Counter]+1} and [Date]=ChType[Date]{rec[Counter]+1}) or ([Person]="" and [Date]=ChType[Date]{rec[Counter]+1}))[Increase]{0}+1) ],
        each [NewSallary]
    ),
    FinalTable = Table.FromColumns(Table.ToColumns(ChType)&{CreateColumn},  Table.ColumnNames(ChType)&{"New Sallary"})
in
    FinalTable

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[l3montrees]

thank you for the reply @Jimmy801 

slighlty confused with the suggested solution. i have all the data in one table. shall i separate them?

I am attaching a sample of the data along with a manual desired solution for more clarity on the existing setup. so the salary might increase at a certain month with a given % that is the same for all staff members --> growth by month % or it might increase again at any given month based on  the staff's rank ie the % increase is not the same for all staff members. so in effect we examine 2 scenarios.

in addition the initial salary might change at some point within the budget year because of a promotion. if you check on the employee 101 had an initial salary of 9k till sept and then due to a promotion his salary went to 12k. in that case we need to compound the initial salary from jan till september at the % growth occured earlier in time and next reset his salary to 12k from Oct and onwards without applying any compounding unless there is a new growth % increase triggered during the last qtr of year in which case we will reset the compounding to 1 at oct and apply the new % growth on the new initial salary.

so somehow there should be a mechanism to reset the compounding in case the initial salary goes up - let's assume that we examine only promotions ie increase on salary - and also take into consideration the timing of the changes.

please check the files in the link below:

https://www.dropbox.com/sh/qao1pjwsvre1222/AAC1CYzBqzCuQLIpyXXQ03eua?dl=0 

thank you in advance for your help!

[Jimmy801 · Accepted solution]

Hello @l3montrees 

now I understand it better. All changes are basically entered on every single row. There is a lot of manual calculation as you have to check always the previous row for changes, and that in Power Query is for sure slow. Maybe using Table.Group can accellerate everything.

I tried to put you a solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZZdagMxDAavUgJ5K8ay1n9nCb3/NZqlbGutpUrfQ0LCDoNAg72v14Nyfnw+mFKmVHI5/3yUVHP+eP+q5+d5Pn8+vj4vuMyUiw7n58/XH3yaOQznlA/EXBFzQ8z9Fz5S9uARht9jTB0+NDNlwEwEzEwlAtPWxrzgfs3MKyzbELBu5jAs2giYK2JuiLkj8EDGmIB5aeO9TE+9xOHSoo7/6LLlwWtL8ugoWx5sh3eZOQyLPALmipgbYu4IvB4d5qF0jTF1mDWzODo8szg6/odvR4cJs93GOEsQbbDdxjDNShsGrLdhm5U2bLPShm1W2rDhgYwxAfPShm8mY4O6ObTBY2uDEv/A5yN5rRxbGwLWzRyGRRsBc0XMDTF3BB7IGBMwr9eKayZjg7o5tMGqtEGF9Daq0sYC62YOw7c2XHNFzA0xdwQeyBgTMIs2PDMZG9TNoQ22rQ1OXI33jba1IWDdzGH4dqe45oqYG2LuCDyQMSZgFneKZyZjg7o5tMGutEHW+0ZX2iDrtrrMHIZvbbjmipgbYu4IPJAxJmAWbXhmMjaom0MbHFsblMZoJ3w2Ju+UsbUhYN3MYfh2p7jmipgbYu4IPJAxJmAWd4pnJmODujm0wam00Y+utzGVNhZYN3MYvrXhmitiboi5I/BAxpiAWbThmcnYoG72Nvj1DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmpId = _t, BudgetMonth = _t, #" initial Salary " = _t, Rank = _t, #"Growth_by_Rank. % Growth" = _t, #"Growth_by_Month.% Growth" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmpId", Int64.Type}, {"BudgetMonth", type date, "de-DE"}, {" initial Salary ", Int64.Type}, {"Rank", Int64.Type}, {"Growth_by_Rank. % Growth", Percentage.Type}, {"Growth_by_Month.% Growth", Percentage.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",0,null,Replacer.ReplaceValue,{"Growth_by_Rank. % Growth", "Growth_by_Month.% Growth"}),
    #"Added Index" = Table.Buffer( Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1)),
    AddRealRank = Table.AddColumn
    (
        #"Added Index",
        "Real rank",
        (row)=> if row[#"Growth_by_Rank. % Growth"]<> null then row[#"Growth_by_Rank. % Growth"] else try if #"Added Index"[#" initial Salary "]{row[Index]-1}<> row[#" initial Salary "] and #"Added Index"[EmpId]{row[Index]-1}= row[EmpId] then 0 else if #"Added Index"[EmpId]{row[Index]-1}<> row[EmpId] then 0 else  null otherwise row[#"Growth_by_Rank. % Growth"],
        Percentage.Type
    ),
    AddRealMonth = Table.AddColumn
    (
        AddRealRank,
        "Real month",
        (row)=> if row[#"Growth_by_Month.% Growth"]<> null then row[#"Growth_by_Month.% Growth"] else try if #"Added Index"[#" initial Salary "]{row[Index]-1}<> row[#" initial Salary "] and #"Added Index"[EmpId]{row[Index]-1}= row[EmpId] then 0 else if #"Added Index"[EmpId]{row[Index]-1}<> row[EmpId] then 0 else  null otherwise row[#"Growth_by_Month.% Growth"],
        Percentage.Type
    ),
    #"Filled Down" = Table.FillDown(AddRealMonth,{"Real rank", "Real month"}),
    AddRank = Table.AddColumn
    (
        #"Filled Down",
        "final sallary rank",
        (row)=> try if #"Filled Down"[#" initial Salary "]{row[Index]-1}<> row[#" initial Salary "] and #"Filled Down"[EmpId]{row[Index]-1}= row[EmpId] then row[#" initial Salary "] else row[#" initial Salary "]*(1+row[Real rank]) otherwise row[#" initial Salary "]*(1+row[Real rank])
    ),
    AddMonth = Table.AddColumn
    (
        AddRank,
        "final sallary month",
        (row)=> try if #"Filled Down"[#" initial Salary "]{row[Index]-1}<> row[#" initial Salary "] and #"Filled Down"[EmpId]{row[Index]-1}= row[EmpId] then row[#" initial Salary "] else row[#" initial Salary "]*(1+row[Real month]) otherwise row[#" initial Salary "]*(1+row[Real rank])
    )
in
    AddMonth

but in your request is very complex and missing logics

why on this lines the growth has no impact on the promotion

and  here it has

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy