Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
joeyrobbins
Frequent Visitor

Matching data by most recent past date in a different table

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 IDStatusInventory
20/09/2022 1NEW0
20/09/2022 2NEW0
20/09/2022 3NEW0
20/09/2022 4NEW0
26/09/2022 1CURRENT3
26/09/2022 2CURRENT4
26/09/2022 3CURRENT2
26/09/2022 4CURRENT1

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 IDStatusInventory
20/09/2022 2NEW0
22/09/2022 4NEW0
24/09/2022 4NEW0
24/09/2022 3NEW0
25/09/2022 1NEW0
26/09/2022 2CURRENT4
27/09/2022 3CURRENT2
28/09/2022 1CURRENT3

 

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

 

Joey

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1664431788826.png

 

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

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1664431788826.png

 

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

That's fantastic, thanks Liu!

joeyrobbins
Frequent Visitor

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?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.