Add constant to the previous value

Here is one way to do it in the query editor with a custom column you can adapt as needed.  This could be done with 3 added columns, but I tried it with one and then expand it to make all 3.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  Note that I first added a Week of Year column to be used in the calculation.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcrBCQAhDETRXnI2mIlRd2sR+29j3YsaCAz8w7wxSEUFa2wvJcIDrAgyNP8XzbSNeqORaVzlMi0y3ZseGBSuOAYlMuaNHTM/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateWeek = _t, max_item_value = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateWeek", type text}, {"max_item_value", Int64.Type}, {"Date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-150"),
    #"Inserted Week of Year" = Table.AddColumn(#"Changed Type with Locale", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Week of Year", "Custom", each let maxvalue = [max_item_value], woy = [Week of Year] in Record.FromList(List.Transform({2,3,5}, each maxvalue + (woy - 48)*_), {"Pessimism", "Most Likely", "Optimism"})),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Pessimism", "Most Likely", "Optimism"}, {"Pessimism", "Most Likely", "Optimism"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Pessimism", Int64.Type}, {"Most Likely", Int64.Type}, {"Optimism", Int64.Type}})
in
    #"Changed Type1"

 

 

Regards,

Pat

It is possible with DAX too of course.  Still add the Week of Year column (in query or with a DAX column) and then use a column expression like this.  You can adapt for the other two columns.