Find closest date before transaction date based on unique ID

[asimpson22]

Hello!

I am a fairly new user to Power Query and am running into an issue. I have been asked to create an excel report where for all cancelled orders we include the previous sale date. From the most basic standpoint, I am transforming the below 2 tables into the third table.

There are a few nuances to this though:

1) A lot can have multiple cancellations. For each cancellation, I will need to identify the most previous order date related to that specific lot.

2) A lot can cancel and resell on the same day. This one is more tricky as we don't have the time stamps for these specific transactions, just the date. A work around that I can think of would be to track if the difference between orders and cancellations is 0 or 1 (gross orders - cancels = 0 then the first transaction of the day must be an order for this lot else a cancel).

I have tried using the following solution from another forum post:

While I believe this solution is very close to what I'm looking for, it does not take into account the UniqueID. It would also neglect to capture the challenge of issue number 2.

I currently have a query for cancellations and a separate query for gross orders with a unique ID and transaction date within each.

Any help would be greatly appreciated! Please let me know if more information is required.

[ronrsnfld]

An example that can be copy/pasted, also showing the issue of multiple order/cancellations on the same date, would be useful.

[asimpson22]

Thanks for your reply! Here are some sample tables and descriptions below. The first 2 tables are what I'm working with and the third table is the result I'm looking for. 

SalesOffice	Lot	UniqueID	Transaction Type	CancelDate
A	1	A1	Cancel	12/2/2022
B	2	B2	Cancel	12/2/2022
C	3	C3	Cancel	12/2/2022
A	1	A1	Cancel	11/1/2022
D	4	D4	Cancel	10/1/2022
E	5	E5	Cancel	9/1/2022
E	5	E5	Cancel	8/15/2022

SalesOffice	Lot	UniqueID	Transaction Type	Sale Date
A	1	A1	Gross Order	11/15/2022
B	2	B2	Gross Order	2/1/2022
C	3	C3	Gross Order	3/1/2022
A	1	A1	Gross Order	10/1/2022
D	4	D4	Gross Order	10/1/2022
D	4	D4	Gross Order	9/1/2022
E	5	E5	Gross Order	9/1/2022
E	5	E5	Gross Order	8/1/2022

SalesOffice	Lot	UniqueID	Transaction Type	CancelDate	Orig. Sale Date
A	1	A1	Cancel	12/2/2022	5/25/2022
B	2	B2	Cancel	12/2/2022	2/17/2022
C	3	C3	Cancel	12/2/2022	1/17/2022
A	1	A1	Cancel	11/1/2022	10/1/2022
D	4	D4	Cancel	10/1/2022	9/1/2022
E	5	E5	Cancel	9/1/2022	9/1/2022
E	5	E5	Cancel	8/15/2022	8/1/2022

Sales Offices A - C are the typical transaction examples with 1 sale and 1 cancel.

Sales Office D is an example of a lot cancelling and then reselling on the same day.

Sales Office E is an example where there were multiple cancellations. This is also an example where a lot resold and then cancelled on the same day.

Please let me know if this helps or if any additional detail would be helpful!

[v-jingzhang · Accepted solution]

Hi @asimpson22 

You may create a new query with below code. 

let
    Source = Table.NestedJoin(CancelTable, {"SalesOffice", "Lot", "UniqueID"}, SalesTable, {"SalesOffice", "Lot", "UniqueID"}, "SalesTable", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let __CancelDate = [CancelDate] in Table.FirstN(Table.Sort(Table.SelectRows([SalesTable], each [Sale Date] <= __CancelDate), {{"Sale Date", Order.Descending}}),1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"SalesOffice", "Lot", "UniqueID", "Transaction Type", "CancelDate", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Sale Date"}, {"Orig. Sale Date
"})
in
    #"Expanded Custom"

Result:

The problem is that from the current sample data, we cannot know which one is a record of a lot cancelling and then reselling on the same day, and which one is a record of a lot resold and then cancelled on the same day. There is no difference in the sample data. So you will find that for the example of Sales Office D, the output is not meeting your expected result. 

I have attached a sample file at bottom. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!