backflash bookings using last qty

[Hansolu]

Hi

i do have a task where I really have no idea how to solve it

We have a production of a part (ArticleID). Each part has several operations (OP) and every OP has a planned worktime per piece.
At some of the operations we put the produced quantity into the system.
e.g. article 1004 does have the OP 10, 20, 30 and 40. At OP 10 and 40 we do the entry into the system. A booking at OP 40 means that OP 20 and OP 30 was also done already but not yet booked. OP10 was also done but is already booked

Now, at the day of entry I need to calculate the used worktime. Issue is, how to I backflash and find all OP between in last Booking point (OP10) and current booking point (OP40) and use the same qty as booked in OP40 for the found OPs (OP 20, OP 30 and OP 40).

The real data is quiet big, not sure how if e.g. a powerquery is the easiest with some fill up/down in terms of performance

thanks

A sample file is here 

Sample file 

[V-yubandi-msft]

Hi @Hansolu ,
Could you let me know if your issue has been resolved or if you still need any more information? If you need further help, please let us know.

[Hansolu]

Hi, 

got some other urgents topic on my hand and did not yet continure, will take another few weeks until I can continue here. I will keep this thread updated, 

thanks

[V-yubandi-msft]

Hi @Hansolu ,
Just checking in to see if you’ve had a chance to look over @GrowthNatives  response. If you have any additional questions, please let us know, we’re here to help. Thank you for being part of the Microsoft Fabric Community Forum.

Best regards,

Yugandhar.

[GrowthNatives · Accepted solution]

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]

[Hansolu]

Hi

thnanks, i will try this over the weekend when I do have time

will give feedback afterwards

[MattiaFratello]

Hi @Hansolu, could you please attach some screenshots?

[Hansolu]

Hi, one screenshot is in the pbix file how it should look like,

Which one do you need else?