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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.