merging current and previous weeks' forecasts

Ok Anonymous , try this code: See images below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdVbboQwDEDRvfA9kh8hxKxlNPvfRo3UVjIV7v0bNEfE5Ebwfm8Wtsb22nSJubi63i8sfPu8HqTtVLphGVTqLmrXhf07p2EZVPqkMpk6k0GlTSrd8eo55/iRoQXW0l328mebvcoue5Ft9jpal/32EFS22eucXfYqu+x1zi57nbPL/mfOp+ylXxuzyi5mkTnI05B1OaOwK1mH7ErWe8rjThYX0LUV64xdxduMqGEwdk34CAOXDlg6aOnApQOXDlg6YOnApQOXDlY6WOn7hKy0U3hCOGX/fWDtRnQKc2Um/aByyETbqNAtUQR9h3AYhEsOtou5MpP5yqAyqJyy6KGAMA8Fk9ehgFKC7PjJmOW6CPqAcCiEh5xsE3NlJl2xXFTmp0PZmIZlUJkfD3pPMSObHtDlq5nBfDVDeEJ4fP8mBwPKPBhUrrv8fAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, SnapshotDate = _t, ForecastDate = _t, Quantity = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"SnapshotDate", type date}, {"ForecastDate", type date}}, "en-BS"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Quantity", Int64.Type}}),
    varCurrentForecastWeek = List.Max(#"Changed Type"[SnapshotDate]),
    varAllValidWeeks = List.Distinct(#"Changed Type"[ForecastDate]),
    varMissingWeeks = 
        List.Difference(
            varAllValidWeeks,
            Table.SelectRows(
                #"Changed Type",
                each [SnapshotDate] = varCurrentForecastWeek
            )[ForecastDate]
        ),
    varMissingForecastsData = 
        Table.SelectRows(
            Table.SelectRows(
                #"Changed Type",
                each [SnapshotDate] <> varCurrentForecastWeek
            ),
            each List.Contains(varMissingWeeks, [ForecastDate])
            ),
    #"Grouped Rows" = 
        Table.Group(varMissingForecastsData, 
        {"ForecastDate"}, 
        {
            {
                "AllRows",
                each _,
                type table [Product=nullable text, SnapshotDate=nullable date, ForecastDate=nullable date, Quantity=nullable number]
            },
            {
                "Latest Forecast",
                each Table.Max(_, "SnapshotDate")
            }
        }
    ),
    #"Expanded Latest Forecast" = Table.ExpandRecordColumn(#"Grouped Rows", "Latest Forecast", {"Product", "SnapshotDate", "Quantity"}, {"Product", "SnapshotDate", "Quantity"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Latest Forecast",{"ForecastDate", "Product", "SnapshotDate", "Quantity"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Product", type text}, {"SnapshotDate", type date}, {"Quantity", Int64.Type}}),
    FinalData = 
        Table.Combine(
            {
                Table.SelectRows(#"Changed Type", each [SnapshotDate] = varCurrentForecastWeek),
                #"Changed Type1"
            }
        )
in
    FinalData

So it returns this (I sorted for this image - the way I combined the tables the "old" data will be at the bottom but that is irrelevant to DAX when it loads)

 

So if I have all of your data correctly

1. the Dec 28 forecast is missing the Dec 21, 14, and 7 data. The Dec 21 data is missing Dec 14 and 7, etc.
2. It pulls the forecast for the latest forecast - Dec 28 varCurrentForecastWeek
3. It finds all dates in the [ForecastDate] field and creates a distinct list of those dates. varAllValidWeeks
4. It makes a list of all dates in that #3 list and sees which ones are missing. varMissingWeeks
5. It then pulls all forecast across all snapshots for any [ForecastDate] that is in varMissingWeeks. varMissingForecastData
6. I then group the varMissingForecastData by [ForecastDate] and create a record for each one that pulls the row for that ForecastDate based on the most recent [SnapshotDate]. GroupedRows
7. I then use the UI to just expand that record, keep the columns I want, and reset the data types. Now I am down to Changed Type1 step.
8. The FinalData step appends the forecast for the latest [SnapshotDate] based on varCurrentWeekForecast and the missing forecast data now in table format in the Changed Type1 step.

This should be fully dynamic into the future.

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.