cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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

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.

1 ACCEPTED SOLUTION
Super User I
Super User I

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,




Feel free to connect with me:
LinkedIn

View solution in original post

1 REPLY 1
Super User I
Super User I

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,




Feel free to connect with me:
LinkedIn

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors