Help With Max Date by Customer Code Previous to Year of Current Record

[e2matt]

I need some help with some logic in Power Query.

I have a table that has many columns but related to this are [CUSTOMER CODE], [BILL DATE]. The table contains a row for each order for many customer codes.

The field I am trying to create would have the date of the last order for the customer code where the year is less than the current orders bill date year. IE if the current order is today, there was an order yesterday and before that the previous order was 2017, I would want to see the 2017 date in this field.

[Smauro · Accepted solution]

Hi @e2matt 

You could try this:

    GroupDates = Table.Group(Table.SelectColumns(PreviousStep, {"CUSTOMER CODE", "BILL DATE"}), {"CUSTOMER CODE"}, {{"Dates", each _[BILL DATE], type list}}),
    MergeDatesperCustomer = Table.NestedJoin(PreviousStep, {"CUSTOMER CODE"}, GroupDates, {"CUSTOMER CODE"}, "Data", JoinKind.LeftOuter),
    #"Expanded Data" = Table.ExpandTableColumn(MergeDatesperCustomer, "Data", {"Dates"}, {"Dates"}),
    AddPreviousOrder = Table.AddColumn (#"Expanded Data", "Previous Order Date in LY", each let bDate = [BILL DATE] in List.Max(List.Select([Dates], each Date.Year(_) < Date.Year(bDate))), type date),
    RemoveDateList = Table.RemoveColumns(AddPreviousOrder, {"Dates"})

Which basically first gets all the dates of a Customer Order, merges them back on the table and then computes the desired result, removing the extra column as a last step.

'PreviousStep' is your last step in the code.

Cheers,