backflash bookings using last qty

Hi Hansolu , this is a classic backflushing problem in production reporting, you need to infer the implied completion of intermediate operations when a later operation is booked, using the latest available entry as reference.

Objective

For each ArticleID:

• When a later operation (say OP40) is booked,

• You want to “backflush” and assign the same quantity to all unbooked operations between the previous booking (e.g. OP10) and the current one (e.g. OP40).

• Then calculate worktime used = PlannedTime × Quantity for those operations.
  
  

🧩 Approach

1. Sort and structure your base data

In Power Query:

• Sort by ArticleID → OP ascending → BookingDate ascending.

• Ensure you have these fields:

  • ArticleID

  • OP

  • BookingDate (or entry date)

  • PlannedTimePerPiece

  • BookedQty (null if not booked)
    
    

2. Identify booking points

Create a flag column:

IsBooked = if [BookedQty] <> null then 1 else 0

This lets you mark where bookings exist.

3. Fill “last booked operation” forward

Use Power Query’s Fill Down logic:

• Add an index column for order.

• Group by ArticleID.

• Within each group:

  • Fill down the most recent booked operation number (LastBookedOP).

  • Also fill down booked quantity (LastBookedQty).

Now every row (operation) will know:

• Which was the last booking point before it,

• And what quantity was booked.
  
  

4. Determine which operations to backflush

Add a conditional column:

ShouldBackflush =
    if [IsBooked] = 1 then 1
    else if [OP] > [LastBookedOP] and [NextBookedOP] <= [OP] then 1
    else 0

This marks all intermediate OPs (e.g., OP20, OP30) that fall between the last and next booking.

Tip: To get NextBookedOP, perform a reverse fill-down (fill-up technique): sort descending, fill down the next booked OP number, then merge it back.

5. Assign backflushed quantity

BackflushQty = if [IsBooked] = 1 then [BookedQty] else [NextBookedQty]

6. Calculate used worktime

UsedWorktime = [PlannedTimePerPiece] * [BackflushQty]

7. (Optional) Optimize performance

Given your data is large:

• Perform grouping and fill operations per ArticleID (not globally).

• Avoid nested Table.Buffer() calls unless necessary.

• If you have SQL backend access, consider doing the backflushing logic in SQL before Power BI import — it’s faster.
  
  

Alternative (DAX approach, if required)

If you need to calculate this dynamically in Power BI (not precomputed in PQ):

1. Create a measure to detect the latest booking OP for each article/date:

   LastBooking = 
   CALCULATE(
       MAX('Table'[OP]),
       FILTER('Table', 
           'Table'[ArticleID] = SELECTEDVALUE('Table'[ArticleID]) &&
           NOT(ISBLANK('Table'[BookedQty]))
       )
   )

2. Then use that measure to conditionally assign quantities or times:

   UsedWorktime =
   VAR CurrentOP = SELECTEDVALUE('Table'[OP])
   VAR LastBookedOP = [LastBooking]
   RETURN
   IF(CurrentOP <= LastBookedOP,
       SUMX(
           FILTER('Table', 'Table'[OP] <= LastBookedOP),
           'Table'[PlannedTimePerPiece] * 'Table'[BookedQty]
       )
   )

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]