Pulling most recent date from related table based on condition

[rachaelwalker]

I am looking for help creating a dax formula based on the tables below. I want to create a column/measure in the Inventory List table to pull the most recent purchase and received date for each item_id, and for the corresponding warehouse and warehouse bin ID. The tables are related using item_id. 

I attempted to create a column to pull the max purchased date but it is returning the same date for all warehouses. 

[VahidDM]

Hi @rachaelwalker 

Could you please add more details to your request? or share sample of your tables here [after removing sensitive data]

How to Get Your Question Answered Quickly

✌️

[rachaelwalker]

@VahidDM Please see above for sample data 

[rachaelwalker]

Here is some sample data for one inventory item. I am looking for the most recent purchase date for each item in each warehouse and warehouse bin. For this data set, it should only return a date for the Oregon warehouse/bin O-4

[PaulDBrown]

Can you copy and paste that data from Excel into your post please?

[rachaelwalker]

Inventory List

IV_Item_RecID	Warehouse_Name	Bin_Name	Qty_On_Hand
31143	Oak Creek Warehouse	Busey Stock	0
31143	Oak Creek Warehouse	J-3	0
31143	Oak Creek Warehouse	K-2	0
31143	Oak Creek Warehouse	K-3	0
31143	Oak Creek Warehouse	Warehouse	0
31143	Oregon Warehouse	O-4	27

Purchase Details

item_id	Date_Purchased	Warehouse_Name	Bin_Name	iv_item_recid
RLNK-215R	6/24/2021	Oregon Warehouse	O-4	31143
RLNK-215R	1/27/2021	Oregon Warehouse	O-4	31143
RLNK-215R	1/27/2021	Oregon Warehouse	O-4	31143
RLNK-215R	12/14/2020	Oregon Warehouse	O-4	31143
RLNK-215R	12/28/2020	Oregon Warehouse	O-4	31143
RLNK-215R	10/13/2020	Oregon Warehouse	O-4	31143
RLNK-215R	10/12/2020	Oregon Warehouse	O-4	31143
RLNK-215R	10/7/2020	Oregon Warehouse	O-4	31143
RLNK-215R	9/18/2020	Oregon Warehouse	O-4	31143
RLNK-215R	9/18/2020	Oregon Warehouse	O-4	31143
RLNK-215R	9/18/2020	Oregon Warehouse	O-4	31143
RLNK-215R	6/30/2020	Oregon Warehouse	O-4	31143
RLNK-215R	3/10/2020	Oregon Warehouse	O-4	31143
RLNK-215R	3/10/2020	Oregon Warehouse	O-4	31143

[rachaelwalker · Accepted solution]

I figured it out by using the group by feature in power query then used the Max/Min on the dates in the visual. Sorry, I should have provided more sample data.