Rolling HC with percentaje

[gadielsolis]

Hello Community,

I'm attempting to project HeadCount using an attrition assumption per week. For example, if my HC is 40 for this week, I want to project a 2% attrition for next week, and for the week after, a 3% attrition (where the 3% is applied to the remaining HC, i.e., the initial 40 minus the 2% from the previous week). Additionally, I will be adding HC in some weeks, so the percentage should be applied to the remaining HC plus the new HC. Please refer to the screenshot below for reference.

Please use the following dataset as an example of the desired outcome:

https://docs.google.com/spreadsheets/d/15woR92oTnJAPMPK0Q3N1x8EKjNDWU--Y/edit?usp=sharing&ouid=11165...

I would be very grateful if anyone could help me with this, as I've been trying to accomplish it for a long time.

If I haven't been clear enough or if you need additional information, please reply to this thread, and I will provide the necessary details.

Thank you in advance.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdPbasMwDAbgdwn0ri6W7NjOs5RetF1WOrZk7AR7+wki15N/yNWHIutgH48DsXueL8N+kI93w2kvNAFxAoru7fyxSdD/SIn8wft/XlpozTc2GjXKnS9Wigprulre2CJrOkaagJKc+WsrDkDsleqpST23UKXgXr4XI+QbFaUMxFGpHlFDhV+3SKoT6EWa70maf1BrfqNudDK522p3zY3Gx/p7kvUDuc/53UxuAqEExEEpaGVZZ+lbqFJ26/XLCMVGtQxCKkDRLeuP7ZuQCpBcUyD3dL8amUCk756k756k5Z6Sm5fZpgpAckOBMlDAZ+2RMr70CBTg+ZJHypZOfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, #"HC Add" = _t, #"Attrition Assumption" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"HC Add", Int64.Type}, {"Attrition Assumption", Percentage.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "HC", each List.Accumulate({0..[Index]},40,(state,current)=>if current = 0 then state else state*(1-#"Added Index"{current-1}[Attrition Assumption])+(#"Added Index"{current}[HC Add]??0)))
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

NOTE:  Our results differ as you seem to be rounding to two decimals.