Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
l3montrees
Frequent Visitor

Automate Salary Increase calculations for HR Budget

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 !😅

1 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

Jimmy801_0-1601966605884.png

and  here it has

Jimmy801_1-1601966619500.png

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

 

View solution in original post

3 REPLIES 3
Jimmy801
Community Champion
Community Champion

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

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!

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

Jimmy801_0-1601966605884.png

and  here it has

Jimmy801_1-1601966619500.png

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors