Matching data by most recent past date in a different table

[joeyrobbins]

Hi,

I'm trying to match data between two tables based on dates: Orders (table 1) and Products (table 2) - examples are below. They are currently linked using the product unique identifier: 'ID' - as the products database has no duplicate IDs. However I want to introduce a date into this products table so I can link the statuses as they change, depending on the order date, but this will cause it to be linked many-many.

Order Date		ID
20/09/2022		2
22/09/2022		4
24/09/2022		4
24/09/2022		3
25/09/2022		1
26/09/2022		2
27/09/2022		3
28/09/2022		1

Date		ID	Status	Inventory
20/09/2022		1	NEW	0
20/09/2022		2	NEW	0
20/09/2022		3	NEW	0
20/09/2022		4	NEW	0
26/09/2022		1	CURRENT	3
26/09/2022		2	CURRENT	4
26/09/2022		3	CURRENT	2
26/09/2022		4	CURRENT	1

Is there a way I can match the data so that the order table brings in the data for the most recent previous date in the products table? The visual/scenario I'm looking for is the table below:

Order Date		ID	Status	Inventory
20/09/2022		2	NEW	0
22/09/2022		4	NEW	0
24/09/2022		4	NEW	0
24/09/2022		3	NEW	0
25/09/2022		1	NEW	0
26/09/2022		2	CURRENT	4
27/09/2022		3	CURRENT	2
28/09/2022		1	CURRENT	3

Having big struggles trrying to manage this - any help is much appreciated!

Joey

[v-yangliu-msft · Accepted solution]

Hi  @joeyrobbins ,

Here are the steps you can follow：

1. Create calculated table.

Table_1 =
var _1=SELECTCOLUMNS('Table1',"Order Date",[Order Date])
var _2=SELECTCOLUMNS('Table2',"Order Date",[Date])
return
DISTINCT(
UNION(_1,_2))

2. Create calculated column.

ID =
MAXX(FILTER(ALL(Table1),'Table1'[Order Date]='Table_1'[Order Date]),[ID])

Status =
IF(
    'Table_1'[Order Date] >=MAXX(ALL('Table2'),[Date]),
    MAXX(FILTER(ALL('Table2'),'Table2'[Date]=MAXX(ALL('Table2'),[Date])),[Status]) 
    ,MAXX(FILTER(ALL('Table2'),'Table2'[Date]=MINX(ALL('Table2'),[Date])),[Status]))

Inventory =
IF(
   'Table_1'[Order Date] >=MAXX(ALL('Table2'),[Date]),
    MAXX(FILTER(ALL('Table2'),'Table2'[Date]=MAXX(ALL('Table2'),[Date])&&'Table_1'[ID]='Table2'[ID]),[Inventory])
    ,MAXX(FILTER(ALL('Table2'),'Table2'[Date]=MINX(ALL('Table2'),[Date])),[Inventory]))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[joeyrobbins]

That's fantastic, thanks Liu!

[joeyrobbins]

In my head - I'm thinking they still need to be linked by the unique identifier (ID) - but this would surely mean many-many. Is there a way to manipulate the many-many and decide what data you want to pull through based on a date criteria?