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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rhildeb
Frequent Visitor

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

EntityMax Monthly VolumeNumber of Entities
Entity 110000500

 

Table2

Month #Single Entity Volume
1100
290
380
470
560
650
740
830
920
1010
110


Here is what I want my data output to look like:

Month #Combined VolumeNew Entities Needed
110000100
21000010
31000011
4999012
5996013
61000015
7999016
8992017
9998020
10994021
11995029
12991028
13992028
14999029
15999017
16995017
17999018

 

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

1 ACCEPTED SOLUTION

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

rhildeb
Frequent Visitor

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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