Customer Forecasts: Calculating latest value when a record removed from most recent data source

[AliPoTD]

Hello all, 

I have a query that I can't figure out, can anyone help please?

We are sent customer forecasts every few days in a spreadsheet which contains the product requirements by delivery date for the coming weeks. Each forecast file contains multiple products by multiple dates.

If a forecast for a particular product on a particular day is no longer needed, it is omitted from the latest spreadsheet.

For example for Product Code BB22 with a delivery date of the 20/05/2025, 

FileName	File Received Date	Product Code	Delivery Date	Qty
File A	05/05/2025	BB22	20/05/2025	100
File B	10/05/2025	BB22	20/05/2025	150
File C	15/05/2025	BB22	(no record)	(no record)

To show the Customer Forecast values in Power Bi, using the Delivery Date as a slicer, I need to show the latest value by File Received Date.

e.g. 1) if the actual date was the 9th of May and I ran the report with the Delivery Date slicer as the 20th May, I would expect to see:

FileName	File Received Date	Product Code	Delivery Date	Qty
File A	05/05/2025	BB22	20/05/2025	100

e.g. 2) if the actual date was the 12th of May and I ran the report with the Delivery Date slicer as the 20th May, I would expect to see:

FileName	File Received Date	Product Code	Delivery Date	Qty
File B	10/05/2025	BB22	20/05/2025	150

The report is currently doing this, as the use of removing Duplicates based on Product Code and Delivery Date keeps only the latest record.

But, if the actual date was the 16th of May, after the latest file has been received with the BB22 record for the 20th May no longer showing, I should not see any forecast value. 

How do I remove the latest record if a later file has superceded it?

I can't just keep the latest file, as it will only have data for 2 weeeks in it, and i need to see data from several months ago therefore need to keep only the relevant rows of data from all the spreadsheets received.

Can anyone help please? Many thanks. 

[AliPoTD]

Amazing!! Thanks so much, this has worked perfectly. Really appreciate it 🙂

[MarkLaf · Accepted solution]

I put the below together with the following embellished test data:

CombinedWorkbooks

FileName	File Received Date	Product Code	Delivery Date	Qty
File A	5/5/2025	BB22	5/20/2025	100
File B	5/10/2025	BB22	5/20/2025	150
File A	5/5/2025	BB77	5/20/2025	75
File B	5/10/2025	BB77	5/20/2025	100
File C	5/15/2025	BB77	5/20/2025	95
File B	5/10/2025	BB44	5/20/2025	50
File C	5/15/2025	BB44	5/20/2025	60
File A	5/5/2025	BB11	5/15/2025	75
File B	5/10/2025	BB11	5/15/2025	100

Same as your data, BB22 is missing a File C record.

The following code will figure out the expected latest file for each delivery date, then use that to either pull latest order record when from latest file, or construct a 0 Qty order from latest file if no such order exists.

let
    Source = CombinedWorkbooks,

    // Latest related file for each delivery date based on all products
    ExpectedDates = Table.ExpandRecordColumn( 
        Table.Group( 
            Table.Sort( Source, { "File Received Date", Order.Descending } ), 
            "Delivery Date", {
                "MaxFileRow", Table.First, 
                Type.TableRow(Value.Type(Source))
            }
        ), 
        "MaxFileRow", {"FileName","File Received Date"} 
    ),

    // For each Product and Delivery Date:
    GroupAndAddMissing = Table.Group( 
        Source, {"Product Code", "Delivery Date"}, 
        { { 
            "groups", 
            each [ 
                // Get latest order within the group
                LatestOrder = Table.First( Table.Sort( _, {"File Received Date", Order.Descending} ) ), 
                // Get latest expected file for the delivery date (using ExpectedDates table)
                LatestFile = Table.First( 
                    Table.SelectRows( ExpectedDates, each [Delivery Date] = LatestOrder[Delivery Date] ) 
                ), 
                // Does latest file for this order match latest expected file?
                DatesMatch = LatestFile[File Received Date] = LatestOrder[File Received Date],
                // If latest order is from latest file, use it as is.
                // Otherwise, return a row from latest file with 0 Qty order
                ResultRow = 
                    if DatesMatch then 
                        LatestOrder 
                    else 
                        LatestFile & [ Product Code = LatestOrder[Product Code], Qty = 0 ]
            ] [ResultRow], 
            Type.TableRow( Value.Type( Source ) )
        } } 
    ),
    
    Combine = Table.FromRecords( GroupAndAddMissing[groups], Value.Type( Source ) )
in
    Combine

Result: