Power Query MAX value if between 2 date ranges

Hi JB2010, check this:

Output

Comment: Remove selected part , GroupKind.Local if you do not have procucts sorted, but keep it if you do.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc8xDoMwDIXhu2RGif1sSLgFO2Jq90oV3L9WF7A9Rvr04n/fy/b9vK/XyWUqPBpzA0HtIbUv5ZgcwOyA9ggIjXGDIREIOdA1LSyN/l/M9tDKIwKWB5C6JkAGcC8QPQFiplYgApdpN2gEMTMBl2kLkhZ8JhIImcOOPH4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Price = _t]),
    F = (tbl as table)=>
      [ price = List.Buffer(Table.Column(tbl, "Price")),
        LG = List.Skip(List.Generate(
                    ()=> [ x = -1 ],
                    each [x] < List.Count(price),
                    each [ x = [x]+1, y = List.Range(price, List.Max({0, x-6}), if x < 6 then x+1 else 6) ],
                    each List.Max([y]) )),
        ToTbl = Table.FromColumns(Table.ToColumns(tbl) & {LG}, Value.Type(Table.FirstN(tbl, 0) & #table(type table[Max Price Last 6wks=number], {})))
      ][ToTbl],
    ChangedType1 = Table.TransformColumnTypes(Source,{{"Price", Currency.Type}}, "en-US"),
    ChangedType2 = Table.TransformColumnTypes(ChangedType1,{{"Date", type date}}, "sk-SK"),
    Ad_MaxPriceLast6Wks = Table.Combine(Table.Group(ChangedType2, {"Product"}, {{"T", F, type table}}, GroupKind.Local)[T])
in
    Ad_MaxPriceLast6Wks

Hi JB2010 

Another solution

let
Source = Your_Source,
Join = Table.NestedJoin(Source, {"Product"}, Source, {"Product"}, "Data", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Data", {"Date", "Price"}, {"Date.1", "Price.1"}),
Last_6_weeks = Table.SelectRows(Expand, each [Date]>=[Date.1] and Date.AddWeeks([Date],-6)<=[Date.1]),
Group = Table.Group(Last_6_weeks, {"Product", "Date", "Price"}, 
    {{"Max Price Last 6wks", each List.Max([Price.1]), type nullable number}})
in
Group

Stéphane 

Hey!

In addition to the other solutions, here is a approach that uses a list.

It selects all the records from the same product with a date that is less than 6 weeks (42 days) old. It then takes the List.Max from that nested table.

let
    Source = YOURDATA,
    add_MaxPrice = Table.AddColumn(Source, "MaxPrice", each List.Max(Table.SelectRows(Source, (Row) => (Row[Product] = _[Product]) and (Row[Date] >= Date.AddDays(Date.From(DateTime.LocalNow()), -42)))[Price]))
in
    add_MaxPrice

if any of our solutions did help with your problem, please accept them as a solution and give kudos. This helps other users with the same problem find the solutions quicker!