Related lookup column with added time component

[Quenril]

Hello,

I have a 'Consultant lookup' table which contains the names of Consultants, the Practice they are in, and the date from which they were in that team. Names are repeated due to promotions or moves between Practices...

I then have a 'Client meetings' table which shows the activity over a given period. My dilemma: In order for my slicers to work, I need to add a column to the 'Client meetings' table to include the name of the Practice that the Consultant was in at the time that the client meeting happened - based upon the [Date of meeting] column in the 'Client meetings' table and the 'Effective from date' in the [Consultant lookup] table. I know how to use the Related formula but I don't know how to account for the changing time-element. 

Please can someone help? Dummy tables here:

Consultant lookup dummy.xlsx

CLIENT_MEETING_20240723070228_dummy.csv

Thank you

Quenril

[Ashish_Mathur]

Hi,

Share a single PBI file with both tables loaded there.  Why share 2 seperate Excel files?

[Quenril]

Hello Zhengdong,

This is so helpful and very, very close to what I need... in some instances the results includes multiple Practice names. Ideally I want only the Practice name which the Consultant is currently sat in... is there a way to remove the additional unecessariy information? I only want the last Practice information returned for each Consultant....

thank you

Quenril 

[v-zhengdxu-msft]

Hi @Quenril 

What does it mean that the result contains multiple practice names, and does it mean that there are several practices in a cell?

Please provide screenshots or other forms of the instances where the results include multiple Practice names and the data that these rows refer to in the Consultant lookup and Client meetings tables.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhengdxu-msft]

Hi @Quenril 

Please try this:

Here I add a calculated column in the 'Consultant lookup' table:

NextData =
VAR _NextData =
    CALCULATE (
        MIN ( 'Consultant lookup'[Effective from date] ),
        FILTER (
            ALLSELECTED ( 'Consultant lookup' ),
            'Consultant lookup'[Consultant Name]
                = EARLIER ( 'Consultant lookup'[Consultant Name] )
                && 'Consultant lookup'[Effective from date]
                    > EARLIER ( 'Consultant lookup'[Effective from date] )
        )
    )
RETURN
    IF ( _NextData <> BLANK (), _NextData, TODAY () )

Then add a calculated column in the 'Client meetings' Table:

Column = 
	VAR _currentdate = 'Client meetings'[Date of Meeting]
	VAR _currentConsultant = 'Client meetings'[Consultant Name]
	VAR _vtable = FILTER(
		CROSSJOIN(
			SELECTCOLUMNS(
				'Client meetings',
				'Client meetings'[Date of Meeting],
				'Client meetings'[Consultant Name]
			),
			SELECTCOLUMNS(
				'Consultant lookup',
				'Consultant lookup'[Practice],
				'Consultant lookup'[Consultant Name],
				'Consultant lookup'[Effective from date],
				'Consultant lookup'[NextData]
			)
		),
		'Client meetings'[Consultant Name] = 'Consultant lookup'[Consultant Name] && 'Client meetings'[Date of Meeting] >= 'Consultant lookup'[Effective from date] && 'Client meetings'[Date of Meeting] < 'Consultant lookup'[NextData]
	)
	RETURN
		CONCATENATEX(
			FILTER(
				_vtable,
				'Client meetings'[Date of Meeting] = _currentdate && 'Client meetings'[Consultant Name] = _currentConsultant
			),
			[Practice]
		)

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.