Lookup column of data from another table with time variable

[Quenril]

Hello,

I hope someone can help. I have a 'Client meeting' table in which I am trying to populate the 'Consultant Practice' and 'Consultant Team' columns from my 'Consultant lookup' table via calculated columns. The difficulty is that as individuals get promoted or move teams, their Team and Practice details change and I want it to return the correct Team or Practice result based upon the latest 'Effective date column' in the lookup table compared to the meeting date. 

Below is the formula I am inputting to return the Consultant Team. It is working in many rows, however not in all. I can't see any patterns in temrs of which are/aren't working. Moreover, it sometimes works and sometimes doesn't work for the same Consultant name:

Consultant Team =

VAR SelectedConsultant = 'Client Meetings'[Consultant Name]

VAR MaxEffectiveFromDate =

    CALCULATE(

        MAX('Consultant lookup'[Effective from date]),

        'Consultant lookup'[Consultant Name] = SelectedConsultant,

        'Consultant lookup'[Effective from date] <= MAX('Client Meetings'[Date of Meeting])

    )

RETURN

    CALCULATE(

        VALUES('Consultant lookup'[Team]),

        'Consultant lookup'[Consultant Name] = SelectedConsultant,

        'Consultant lookup'[Effective from date] = MaxEffectiveFromDate

    )

Here are the 'Consultant lookup' and 'Client meeting' tables with some of the rows that are working and some that aren't (redacted data):

  Consultant lookup dummy.xlsx

CLIENT_MEETING_20240723070228_dummy.csv

Please can someone help?

Thank you so much

Quenril

[HotChilli]

There's no 'Client Meetings'[Consultant Name] column in the data provided. It's in the calculated column - is it supposed to refer to

Consultant Attendee 1?

What rows show the problem you are experiencing and what are you expecting to see?

[gmsamborn]

Hi @Quenril 

In addition to the change by @HotChilli  , I set up a variable for Meeting Date and then used that in the comparison.

Consultant Team =
VAR SelectedConsultant = [Consultant Attendee 1]
VAR MeetingDate = [Date of Meeting]
VAR MaxEffectiveFromDate =
    CALCULATE(
        MAX('Consultant lookup'[Effective from date]),
        'Consultant lookup'[Consultant Name] = SelectedConsultant,
        'Consultant lookup'[Effective from date] <= MeetingDate
    )
RETURN
    CALCULATE(
        MAX('Consultant lookup'[Team]),
        'Consultant lookup'[Consultant Name] = SelectedConsultant,
        'Consultant lookup'[Effective from date] = MaxEffectiveFromDate
    )

Let me know if you have any questions.

[Quenril · Accepted solution]

Hello,

Thankyou both so much. I have tried both versions and they return exactly the same result as I had before... Do you have any other ideas? The Client table I provided has the Consultants listed as Attendee 1/2 etc, however I've unpivoted that part in Query Editor so when I look at the table in Power BI itself I have a column for 'Lead of Invitee' and then a column called 'Consultant Name' which is the column I want the lookup to use...

Do you have any other ideas please?

Thank you

Becky  

[HotChilli]

I don't like this part:

CALCULATE(
        VALUES

That's going to return a table.

It's possible (though I have never tried it) that you get data being returned if there is only one row being returned from this but not multiple rows or blanks or nulls. Just a theory but you can investigate at your side.