Forum Discussion
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 100, declining each month until month 10 when it produces a volume of 10. After month 10, the entity produces 0.
I have a facility that can handle a monthly volume of 10,000. I want to keep the facility as full as possible, but can't exceed 10,000. So the challenge is determining how many entities I can start producing each month.
Here is a simplified version of my data:
Table1
| Entity | Max Monthly Volume | Number of Entities |
| Entity 1 | 10000 | 500 |
Table2
| Month # | Single Entity Volume |
| 1 | 100 |
| 2 | 90 |
| 3 | 80 |
| 4 | 70 |
| 5 | 60 |
| 6 | 50 |
| 7 | 40 |
| 8 | 30 |
| 9 | 20 |
| 10 | 10 |
| 11 | 0 |
Here is what I want my data output to look like:
| Month # | Combined Volume | New Entities Needed |
| 1 | 10000 | 100 |
| 2 | 10000 | 10 |
| 3 | 10000 | 11 |
| 4 | 9990 | 12 |
| 5 | 9960 | 13 |
| 6 | 10000 | 15 |
| 7 | 9990 | 16 |
| 8 | 9920 | 17 |
| 9 | 9980 | 20 |
| 10 | 9940 | 21 |
| 11 | 9950 | 29 |
| 12 | 9910 | 28 |
| 13 | 9920 | 28 |
| 14 | 9990 | 29 |
| 15 | 9990 | 17 |
| 16 | 9950 | 17 |
| 17 | 9990 | 18 |
In case the explanation wasn't totally clear, an example is provided below. The numbers in red are what I'm interested in.
- Month 1: 100 ent. * 100 vol. = 10,000 capacity
- Month 2: 100 ent. * 90 vol + 10 ent * 100 vol. = 10,000 capacity
- Month 3: 100 ent. * 80 vol + 10 ent * 90 vol. + 11 ent * 100 vol. = 10,000 capacity
- Month 4: 100 ent. * 70 vol + 10 ent * 80 vol + 11 ent * 90 vol + 12 ent * 100 vol. = 9,990 capacity
Thanks in advance for any help!!!!
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.
5 Replies
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.
