Forum Discussion
Keeping Capacity Full When Entities Have Declining Volume
That requires a bit of coding and I made it fully dynamic, so you can adjust all relevant parameters:
let // Function, can be in a seprarate query as well fnProductionAllocation = (MaxMonthlyVolume as number, NumberOfEntities as number, MaxCapacity as number, MonthlyReduction as number) => let Duration = MaxCapacity/MonthlyReduction, ListOfPeriods = {1..Duration}, #"Converted to Table" = Table.FromList(ListOfPeriods, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Amount", MaxCapacity, -MonthlyReduction), Rename = Table.RenameColumns(#"Added Index",{{"Column1", "Period"}}), ProductionScheme = Table.AddColumn(Rename, "SourcePeriod", each 0), StartingTable = #table({"Period", "Amount"}, {{1,0}}), ProductionPlan = List.Generate( ()=> [ProdTable = StartingTable, TotalCapacity = 0, NewCapacity = 0, RemainingEntities = NumberOfEntities, Period = 0], each [NewCapacity]<>0 or [Period]=0, each [ Period = [Period]+1, StartingQty = List.Sum(Table.SelectRows([ProdTable], each [Period]=Period)[Amount]), NewCapacity = List.Min({[RemainingEntities],Number.RoundDown((MaxMonthlyVolume - StartingQty) / MaxCapacity)}), TotalCapacity = StartingQty + NewCapacity*MaxCapacity, CapacityForecast = Table.TransformColumns(ProductionScheme, {{"Amount", each _ * NewCapacity, type number}}), TransformSourcePeriod = Table.TransformColumns(CapacityForecast, {{"SourcePeriod", each Period, type number}}), ProdTable = Table.SelectRows([ProdTable] & Table.TransformColumns(TransformSourcePeriod, {{"Period", (x)=> x + [Period], type number}}), each [Period]>=Period), RemainingEntities = [RemainingEntities] - NewCapacity ]), #"Converted to Table1" = Table.FromList(ProductionPlan, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"ProdTable", "TotalCapacity", "NewCapacity", "RemainingEntities", "Period", "StartingQty", "CapacityForecast"}, {"ProdTable", "TotalCapacity", "NewCapacity", "RemainingEntities", "Period", "StartingQty", "CapacityForecast"}) in #"Expanded Column1", // FunctionCall in a table Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0rySypVDBU0lEyNAACIG0KJGN14FJGECGQjBlIJhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Entity = _t, #"Max Monthly Volume" = _t, #"Number of Entities" = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Number of Entities", type number}, {"Max Monthly Volume", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "ProductionAllocation", each fnProductionAllocation([Max Monthly Volume],[Number of Entities],100,10)), #"Expanded ProductionAllocation" = Table.ExpandTableColumn(#"Added Custom", "ProductionAllocation", {"ProdTable", "TotalCapacity", "NewCapacity", "RemainingEntities", "Period", "StartingQty"}, {"ProdTable", "TotalCapacity", "NewCapacity", "RemainingEntities", "Period", "StartingQty"}) in #"Expanded ProductionAllocation"BTW: I think that your sample values are not correct from period 11 onwards.
I think the solution here is pretty simple. Since your entities have to start at 100, and each entity has a linear drop off, the solution will always be to add 1 new entity for each 100 spare capacity at your location. If you are at capacity this month, you will always have spare capacity next month. Your entities never increase, so there is no risk adding a new entity on.
If your data tables contain the month date, and each time you add an entity you create all 11 rows, you could simply check the capacity of the first month you care about. Add 1 entity per each 100 spare capacity. Add those 11 rows for each added entity.
Now check the next month and perform the same operation.
Ross, thanks for the response! Are you talking about doing this row addition manually or how would it look from a practical sense?
In my example, I said there was 1 facility, that there are 500 entities, and that they only produce for 10 months. In reality, there is multiple facilities, 1000s of entities, and they produce for many years. So I'm looking for an efficient way to manage this.
If your suggestion is (somewhat) automated, could you elaborate a bit so that I can understand how I actually build this? Thanks!
Hi rhildeb,, This one is quite a tricky one to solve in DAX, so I've let Imke know. I can probably do this in PQ for you but I suspect her solution would be more elegant than mine.
That requires a bit of coding and I made it fully dynamic, so you can adjust all relevant parameters:
let // Function, can be in a seprarate query as well fnProductionAllocation = (MaxMonthlyVolume as number, NumberOfEntities as number, MaxCapacity as number, MonthlyReduction as number) => let Duration = MaxCapacity/MonthlyReduction, ListOfPeriods = {1..Duration}, #"Converted to Table" = Table.FromList(ListOfPeriods, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Amount", MaxCapacity, -MonthlyReduction), Rename = Table.RenameColumns(#"Added Index",{{"Column1", "Period"}}), ProductionScheme = Table.AddColumn(Rename, "SourcePeriod", each 0), StartingTable = #table({"Period", "Amount"}, {{1,0}}), ProductionPlan = List.Generate( ()=> [ProdTable = StartingTable, TotalCapacity = 0, NewCapacity = 0, RemainingEntities = NumberOfEntities, Period = 0], each [NewCapacity]<>0 or [Period]=0, each [ Period = [Period]+1, StartingQty = List.Sum(Table.SelectRows([ProdTable], each [Period]=Period)[Amount]), NewCapacity = List.Min({[RemainingEntities],Number.RoundDown((MaxMonthlyVolume - StartingQty) / MaxCapacity)}), TotalCapacity = StartingQty + NewCapacity*MaxCapacity, CapacityForecast = Table.TransformColumns(ProductionScheme, {{"Amount", each _ * NewCapacity, type number}}), TransformSourcePeriod = Table.TransformColumns(CapacityForecast, {{"SourcePeriod", each Period, type number}}), ProdTable = Table.SelectRows([ProdTable] & Table.TransformColumns(TransformSourcePeriod, {{"Period", (x)=> x + [Period], type number}}), each [Period]>=Period), RemainingEntities = [RemainingEntities] - NewCapacity ]), #"Converted to Table1" = Table.FromList(ProductionPlan, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"ProdTable", "TotalCapacity", "NewCapacity", "RemainingEntities", "Period", "StartingQty", "CapacityForecast"}, {"ProdTable", "TotalCapacity", "NewCapacity", "RemainingEntities", "Period", "StartingQty", "CapacityForecast"}) in #"Expanded Column1", // FunctionCall in a table Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0rySypVDBU0lEyNAACIG0KJGN14FJGECGQjBlIJhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Entity = _t, #"Max Monthly Volume" = _t, #"Number of Entities" = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Number of Entities", type number}, {"Max Monthly Volume", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "ProductionAllocation", each fnProductionAllocation([Max Monthly Volume],[Number of Entities],100,10)), #"Expanded ProductionAllocation" = Table.ExpandTableColumn(#"Added Custom", "ProductionAllocation", {"ProdTable", "TotalCapacity", "NewCapacity", "RemainingEntities", "Period", "StartingQty"}, {"ProdTable", "TotalCapacity", "NewCapacity", "RemainingEntities", "Period", "StartingQty"}) in #"Expanded ProductionAllocation"BTW: I think that your sample values are not correct from period 11 onwards.
ImkeF, thanks so much for the response! I really appreciate your effort to put this together. (And I apologize for the slow reply, but I had to learn more about PowerBI just to get this to work. The links you provided were very helpful).
Thanks!
