Calculating Rolling Locked Forecast Values

Hi chris_m,

Based on your information, I can only get the latest version for each week by following the steps.

1. I type the forecast versions, and split the column into two column in query editor, please see my Query statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1BCoAwEEPRu3StkERhOmcpvf81nIUUDd0+PvwxmsBAKk6AbR4LsiAWELh+RcFtQDrIIQ1sy0octAN9oXvxXuYD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [version = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"version", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [version]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom.2", "number"}})
in
    #"Renamed Columns"

You will get the following structure data.



2. Transfer the custom.1 column to date format and create several calculated using DAX formulas below.

Date = DATE(LEFT(Forecast[Custom.1],4),RIGHT(LEFT(Forecast[Custom.1],6),2),RIGHT(Forecast[Custom.1],2))

weeknum = WEEKNUM(Forecast[Date])

Latest_number =
CALCULATE (
    MAX ( Forecast[number] ),
    FILTER (
        Forecast,
        Forecast[weeknum] = EARLIER ( Forecast[weeknum] )
            && Forecast[Date] = EARLIER ( Forecast[Date] )
    )
)


lastest_date in one week =
CALCULATE (
    MAX ( Forecast[Date] ),
    FILTER ( Forecast, Forecast[weeknum] = EARLIER ( Forecast[weeknum] ) )
)

You will get the following result.



3. Please click "New Table" under modeling on home page, you will get the latest version each week.

New =
SELECTCOLUMNS (
    FILTER (
        Forecast,
        Forecast[Date] = Forecast[lastest_date in one week]
            && Forecast[number] = Forecast[Latest_number]
    ),
    "weeknumber", Forecast[weeknum],
    "latest_version", Forecast[version]
)

In addition, you said you use latest veriosn of each week to determine the forecast volume for a period in the future (6 weeks / or 42 days), I am confused about your this requirement, what's the calculation rules? Could you please share more details for further analysis?

Best Regards,
Angelia