Forum Discussion
Solved
Keeping Capacity Full When Entities Have Declining Volume
My problem is simple, getting the calculations done in Power BI however seems to be a much bigger challenge. I have 500 entities. Each entity will produce for 10 months, starting with a volume of...
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.
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!