Lookup with two criteria

[Power_It_Up]

Hi there.  Converting from Tableau, so please be gentle!

I have two tables:-

In the ORDER table, I am wanting to return the nearest [DEPT] match based on the DEPT table.

So for Case Refs 123 and 456, it should return "A" as the [Order Date] falls between the [Date Change] dates in the DEPT table.

Cases 789 and 987, returns "B" and so forth.

I have the DEPT table sorted by [Name] then ascending [DATE CHANGE].

Many thanks for your valued advice in advance. 🙂

[Power_It_Up]

@Jihwan_Kim Many thanks for your reply.  This helped me to resolve my issue. 🙂

[Jihwan_Kim · Accepted solution]

Hi, I am not sure how your datamodel looks like but please check the below if it suits your requirement.

It is for creating a new column.

Department CC =
MAXX (
    FILTER (
        Department,
        Department[Date Change]
            = MAXX (
                FILTER (
                    Department,
                    Department[Name] = 'Order'[Name]
                        && Department[Date Change] <= 'Order'[Order Date]
                ),
                Department[Date Change]
            )
    ),
    Department[Dept]
)