Need Help with MAX Date with multiple conditions

[Crydus]

Hello,

I need some help regarding a calculated column which returns me the MAX(X) date with a second set of filters. I have basic knowledge on how to use the MAXX()-Expression for that, but I don't know how and if I can add another filter to this expression. 

Our company has a list of prices (item_customer_relationship) with valid_from and valid_to dates, with the limitation that valid_to is not really in use anymore (I will provide an example belw with the basic idea of this table.) My problem now is that I have to identify the current price which is valid. 
I now need a calculated column which returns the values found in remark. The only thing I manage to get is the max valid_from Date per Item but I do not now how to include the valid_to date. 


Currently our system basically has 3 options: 
Item0001 is the best case, with all dates filled, Item0002 is the case where valid_to is implemented for the next price change, but currently it is not in use, Item0003 is the case where No valid_to date is provided. 

Item Key	Valid From	Valid To	Remark
Item0001	01.04.2024	01.04.2025	Valid in Future
Item0001	01.04.2023	01.04.2024	Valid
Item0001	01.04.2022	01.04.2023	Not Valid
Item0002	01.04.2024	01.04.2025	Valid in Future
Item0002	01.04.2023		Valid
Item0002	01.04.2022		Not Valid
Item0003	01.04.2024		Valid in Future
Item0003	01.04.2023		Valid
Item0003	01.04.2022		Not Valid

I hope that you can help me. Please feel free to ask for more information, I will provide as much as I can if anything is needed. 

[amitchandak · Accepted solution]

@Crydus , A new column in a sales/transaction table

Price from item to sales =

Maxx(filter(Item , Item[Item Key] = sales[Item Key] && Sales[Date]>= Item[Valid From] && Sales[Date]<= Item[Valid To]) , Item[Price])