Production planning on the timeline acc to establish assupmption

[akslagzim]

Hi,

does anybody know if it is possible to write a measure that would enable production planning on the timeline according to established assumption?

I created a chart which shows weekly number of components demand (column) versus production capacity (line).

If at the particular week demand > capacity, then I would like to move the difference (capacity - demand) to the previous week.

What I have (y = 40)

What I need

Week 51 = 40 (12 go to Week 50)

Week 50 = 12

Week 49 = 20

Week 48 = 16

Week 47 = 40

Week 46 = 40

Week 45 = 40

Week 44 = 40

Week 43 = 40

from Week 43 to Week 47 we have 174 pcs to plan, so

Week 42 = 40

Week 41 = 40

Week 40 = 40

Week 39 = 40

Week 38 = 14

etc.

I have tried to writa a measure (below), but it does not distribute surplus overk week with free slots.

ProdPlan =

VAR PrevWeek = SELECTEDVALUE(_Calendar[FirstDayOfWeek]) - 7

VAR ThisWeek = SELECTEDVALUE(_Calendar[FirstDayOfWeek])

VAR NextWeek = SELECTEDVALUE(_Calendar[FirstDayOfWeek]) + 7

VAR SurplusNextWeek = [Capacity] - CALCULATE([Demand] , _Calendar[FirstDayOfWeek] <= NextWeek)

RETURN

IF(CALCULATE([Demand] , _Calendar[FirstDayOfWeek] <= NextWeek) > [Capacity] ,

    [Demand] + SurplusNextWeek ,

    [Demand])

I would appreciate any help!

[akslagzim]

Hi,

Table.Buffer has passed the exam.

Going further, is it possible to make the efficiency value variable depended on week? This variable would be defined in extra column "Efficiency".

[lbendlin]

Yes, you can use parameters or prior code steps.

[akslagzim]

Hi,

really appreciate your help!

I have done all the steps but get stuck at "Expended capacity" which loading takes ages and no end in sight 😞 Normally refreshing of this data takes seconds.

[lbendlin]

There's not much you can do.  Maybe a Table.Buffer around #"Added Index". But with 21.3GB even that may not work as you may not have that much memory.

Power BI is a reporting tool, not a resource planning tool.

[akslagzim]

---

@lbendlin wrote:

Power BI is a reporting tool, not a resource planning tool.

---

Of course, and for the record - this analysis was supposed to be auxiliary report for the plant supplying one of components, not the main tool for production planning of our main product.

I narrowed down the analysis period and managed to load the data 🙂 Thanks!

[lbendlin · Accepted solution]

Trying to do conditional aggregations in DAX is futile.  The only function that can do that is Power Query's List.Accumulate.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "Rc3LDQAgCAPQXTh78AOosxj3X8MiJNweTVPOIWlUSDrdAle4fvIGe3jBTd0TRvCtlnOUBIdGiW0zPCxnd8/9lsyvYydXcibVeR8=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Week = _t, Demand = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Demand", Int64.Type}}), 
  #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), 
  #"Added Custom" = Table.AddColumn(
    #"Added Index", 
    "Capacity", 
    each List.Accumulate(
      {0 .. [Index]}, 
      [gross = 0, net = 0], 
      (state, current) => [
        gross = state[gross] + #"Added Index"{current}[Demand] - state[net], 
        net = 
          if state[gross] + #"Added Index"{current}[Demand] - state[net] > 40 then
            40
          else
            state[gross] + #"Added Index"{current}[Demand] - state[net]
      ]
    )
  ), 
  #"Expanded Capacity" = Table.ExpandRecordColumn(#"Added Custom", "Capacity", {"net"}, {"net"}), 
  #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Capacity", {{"net", Int64.Type}})
in
  #"Changed Type1"

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 entire Source step with your own source.