merging current and previous weeks' forecasts

[Anonymous]

Hi everyone,

I have a sales forecast table which creates a new forecast every week, while still retaining the old forecasts. Each forecast has a 'snapshot date', meaning the date the forecast was created, and then another forecast date column referring to the weeks the sales are being forecast to take place.

The earliest date which the 'forecast date' can refer to is the current week, so if the snapshot date is 07/12, the earliest forecast date is 07/12, and if the snapshot date is 14/12, the earliest forecast date is 14/12 and so on as shown for one product below.

Product	SnapshotDate	ForecastDate	Quantity
18173	07/12/2020	07/12/2020	182
18173	07/12/2020	14/12/2020	182
18173	07/12/2020	21/12/2020	182
18173	07/12/2020	28/12/2020	182
18173	07/12/2020	04/01/2021	182
18173	07/12/2020	11/01/2021	182
18173	07/12/2020	18/01/2021	182
18173	07/12/2020	25/01/2021	182
18173	07/12/2020	01/02/2021	182
18173	07/12/2020	08/02/2021	182
18173	07/12/2020	15/02/2021	182
18173	07/12/2020	22/02/2021	182
18173	07/12/2020	01/03/2021	80
18173	14/12/2020	14/12/2020	182
18173	14/12/2020	21/12/2020	182
18173	14/12/2020	28/12/2020	182
18173	14/12/2020	04/01/2021	182
18173	14/12/2020	11/01/2021	182
18173	14/12/2020	18/01/2021	182
18173	14/12/2020	25/01/2021	182
18173	14/12/2020	01/02/2021	182
18173	14/12/2020	08/02/2021	182
18173	14/12/2020	15/02/2021	182
18173	14/12/2020	22/02/2021	182
18173	14/12/2020	01/03/2021	80

What I would like to achieve in Power Query is using the most recent forecast snapshot for the current and future weeks, but use the previous weeks' forecasts for missing weeks. To explain, if the most recent forecast I have is made on 14/12, this is the forecast I want to use for the remaining weeks in December and thereafter, but because we don't confirm 'actual' results until the end of the month, I still want to have a full month's forecast for December, which I cannot get from the forecast made on 14/12, as it doesn't contain 07/12. I would therefore like to have the forecast for the weeks 14/12, 21/12 and 28/12 using the forecast from 14/12, but use the forecast from 07/12 for the forecast week of 07/12. This logic continues, as once it is 21/12, I want to use the forecast with snapshot date of 21/12, but use the forecast from snapshot date of 14/12 for the forecast week of 14/12, and use the forecast from snapshot date of 07/12 for the forecast week of 07/12.

So the logic is: forecast = most recent forecast, but if any previous weeks are missing, use the latest forecast which contains that date.

Hopefully this makes sense. Any help would be much appreciated!

Paddy

[edhans]

Glad I was able to assist @Anonymous 

[edhans]

See if this works @Anonymous 

As best I can tell, Dec 14 has everything you need except the Dec 7 date. I found this by comparing all Dec 14 dates in the forecast column with the forecast dates in the Dec 7 snapshot.

Then I filtered your table twice to get all Dec 14 data, and only Dec 7 data that Dec 14 didn't contain.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdFBCsMwEEPRu3gd8EhJyNwl5P7XaLroQilVtTR+YJl/ngONYx3LqGOCk8V6HtAc1/JDYkslEctOZW2z8D7g707EslPJPZU3K2ayU4k9lWT8+r1z/cgugVraZZdLm12lyy7SZtdpLvvjE6m02XWny67SZdedLrvudNm/dkr26wU=", 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]),
    varPreviousForecastWeek = Date.AddDays(varCurrentForecastWeek, -7),
    varPreviousForecastWeekStart = 
        List.Min(
            Table.SelectRows(#"Changed Type", each [SnapshotDate] = varPreviousForecastWeek)[ForecastDate]
            ),
    varValidWeeks = 
        List.Dates(
            varPreviousForecastWeekStart, 
            Table.RowCount(
                Table.SelectRows(#"Changed Type", each [SnapshotDate] = varPreviousForecastWeek)
                ),
            #duration(7,0,0,0)),
    varMissingWeeks = 
        List.Difference(
            varValidWeeks, 
            Table.SelectRows(#"Changed Type", each [SnapshotDate] = varCurrentForecastWeek)[ForecastDate]
        ),
    #"Current Forecasts" = 
        Table.SelectRows(#"Changed Type", each [SnapshotDate] = varCurrentForecastWeek),
    #"Missing Previous Weeks" = 
        Table.SelectRows(
            Table.SelectRows(#"Changed Type", each [SnapshotDate] = varPreviousForecastWeek),
            each List.Contains(varMissingWeeks, [ForecastDate])
        ),
    Result = Table.Combine({#"Missing Previous Weeks", #"Current Forecasts"})
in
    Result

If that isn't what you want, can you provide specific details on expected output? You can send screenshots of this from Excel if you like. I appreciate the table data to work with. That was excellent.

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.

[Anonymous]

Hi @edhans ,

Thank you for coming back to me so quickly, really appreciate it. 

That's nearly exactly what I'm looking for yes. You're exactly right when you say 'Dec 14 has everything you need except the Dec 7 date'. The only thing is that it has to be a continuous update with each new snapshot date, it won't just be looking at one week in the past.

So below you can see that when the snapshot date is 14/12/20, like you said, it just needs to retain the forecast from the previous week, or -7 days. But when the snapshot date is 21/12/20, I would like to then retain the forecast date of 14/12 from snapshot date of 14/12, and forecast date of 07/12 from snapshot date of 07/12. Same for when the snapshot date is 28/12/20: use the forecast from snapshot date 28/12/20 for current and future weeks, and then the previous snapshots for their latest week. This is so I can still get a full monthly forecast even once previous weeks are no longer included in the newest snapshot. I'm not sure if it's possible to do it dynamically like that.

Hope this makes sense.

Many thanks,

Paddy

[edhans]

Can you post your Dec 21 data so I can append it to what I have? I don't need Dec 7/14 again. But I do need 3 weeks of data to tinker with this. I think this is doable without too many more steps. I might not get to it until tomorrow morning though. 

[Anonymous]

Hi  

Thanks again for your reply. Here is the data from snapshot date 21/12/20 and also 28/12/20, so the remaining weeks of the month. Then this would continue into following months, with the next week being snapshot date 04/01/21.

I think the logic would be: work out the max date on the snapshot date column and keep all data which has that max snapshot date. Then for the remaining data, remove all data apart from the latest week.

Thank you!

18173	21/12/2020	21/12/2020	182
18173	21/12/2020	28/12/2020	182
18173	21/12/2020	04/01/2021	182
18173	21/12/2020	11/01/2021	182
18173	21/12/2020	18/01/2021	182
18173	21/12/2020	25/01/2021	182
18173	21/12/2020	01/02/2021	182
18173	21/12/2020	08/02/2021	182
18173	21/12/2020	15/02/2021	182
18173	21/12/2020	22/02/2021	182
18173	21/12/2020	01/03/2021	80
18173	21/12/2020	08/03/2021	80
18173	21/12/2020	15/03/2021	80
18173	28/12/2020	28/12/2020	182
18173	28/12/2020	04/01/2021	182
18173	28/12/2020	11/01/2021	182
18173	28/12/2020	18/01/2021	182
18173	28/12/2020	25/01/2021	182
18173	28/12/2020	01/02/2021	182
18173	28/12/2020	08/02/2021	182
18173	28/12/2020	15/02/2021	182
18173	28/12/2020	22/02/2021	182
18173	28/12/2020	01/03/2021	80
18173	28/12/2020	08/03/2021	80
18173	28/12/2020	15/03/2021	80
18173	28/12/2020	22/03/2021	80
18173	28/12/2020	29/03/2021	80
18173	28/12/2020	05/04/2021	100
18173	28/12/2020	12/04/2021	100
18173	28/12/2020	19/04/2021	100
18173	28/12/2020	26/04/2021	100
18173	28/12/2020	03/05/2021	80
18173	28/12/2020	10/05/2021	80
18173	28/12/2020	17/05/2021	80
18173	28/12/2020	24/05/2021	80
18173	28/12/2020	31/05/2021	80
18173	28/12/2020	07/06/2021	100
18173	28/12/2020	14/06/2021	100
18173	28/12/2020	21/06/2021	100
18173	28/12/2020	28/06/2021	100
18173	28/12/2020	05/07/2021	100
18173	28/12/2020	12/07/2021	100
18173	28/12/2020	19/07/2021	100
18173	28/12/2020	26/07/2021	100
18173	28/12/2020	02/08/2021	80
18173	28/12/2020	09/08/2021	80
18173	28/12/2020	16/08/2021	80
18173	28/12/2020	23/08/2021	80
18173	28/12/2020	30/08/2021	80
18173	28/12/2020	06/09/2021	100
18173	28/12/2020	13/09/2021	100
18173	28/12/2020	20/09/2021	100
18173	28/12/2020	27/09/2021	100
18173	28/12/2020	04/10/2021	100
18173	28/12/2020	11/10/2021	100
18173	28/12/2020	18/10/2021	100
18173	28/12/2020	25/10/2021	100
18173	28/12/2020	01/11/2021	80
18173	28/12/2020	08/11/2021	80
18173	28/12/2020	15/11/2021	80
18173	28/12/2020	22/11/2021	80
18173	28/12/2020	29/11/2021	80
18173	28/12/2020	06/12/2021	100
18173	28/12/2020	13/12/2021	100
18173	28/12/2020	20/12/2021	100
18173	28/12/2020	27/12/2021	100

[edhans · Accepted solution]

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.

[Anonymous]

genius! Thank you @edhans !