Power Query MAX value if between 2 date ranges

[JB2010]

Hi. Thanks in advance for any help on this.

I have a large set of Sales data with many products, 3 yrs of weekly Price data & a date column. I am trying to find a way in PowerQuery to write an equivalent to an excel MAXIFS that returns an Add Column for the highest Price achieved in the last 6 weeks from the specific row's date in the Date column.

The initial data looks like this...

I've put an additional column in that gives the lower date threshold i want the MAX IF to search between, but i do not know how to code the Add Column to get the MAX value like this for each Product type....

Any help gratefully received!

JB

[dufoq3 · Accepted solution]

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

[slorin · Accepted solution]

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 

[v-pnaroju-msft]

Hi JB2010,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[JB2010]

Hi. I'm very grateful for all the creative ideas offered to fix this & I apologise for the delay in responding; the project this is part of was delayed.

Unfortunately i cannot get any of the ideas offered below to work. I can see that some have been accepted as Solutions, so others must be able to make them work?

I would accept that my coding ability is clearly not of sufficient standard to make this work. I will try another way to do this, or just move the project into excel, where this sort of thing would only take minutes.

Thanks all.

[v-pnaroju-msft]

Hi JB2010,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Thank you, @dufoq3 , @slorin , and @Chewdata , for your response.

Hi @JB2010,

We would like to check if the solution provided by @dufoq3 or @slorin or @Chewdata  has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.

If you found the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to similar queries.

Thank you.

[Chewdata · Accepted solution]

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!

[slorin · Accepted solution]

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 

[dufoq3 · Accepted solution]

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