LOOKUP between two dates with related Email

[Auski]

Hi all,
I have looked at similar threads here for a solution, but haven't been very successful finding a suitable solution. This is somewhat of a non standard lookup....

I have two tables; 'Org Chart' and 'Cases'. The relationship of these tables is "Email".

The 'Org Chart' table contains a WEEKLY snapshot (including history) of my company's organisation chart over the past 5 years -which means there is a lot of duplicate data within the table. This table contains 'Employee Role' and a 'Start of Week' field and an 'End of Week' field.

  

The 'Cases' table contains a case created date, however it does not contain Employee Role

I would like to create a column in Cases that looks at created date and returns the Employee Role BETWEEN the related 'Start of Week' and 'End of Week' fields.

The expected outcome would be:

I hope that makes sense.... please let me know if it is not clear.

Thanks so much 🙂




Cases

Email	Created Date
joe.blow@company.com	25/04/2024
joe.blow@company.com	27/04/2024
joe.blow@company.com	24/04/2024
jane.blow@company.com	26/02/2024
jane.blow@company.com	27/04/2024
joe.blow@company.com	26/04/2024
jane.blow@company.com	22/02/2024
josh.blow@company.com	26/04/2024
jess.blow@company.com	15/03/2023
josh.blow@company.com	27/12/2023
jess.blow@company.com	27/04/2024
jane.blow@company.com	24/04/2024

Org Chart

Email	Start of Week	End of Week	Employee Role
joe.blow@company.com	24/04/2024	1/05/2024	Data Analyst
joe.blow@company.com	24/04/2024	1/05/2024	Data Analyst
jane.blow@company.com	20/02/2024	27/02/2024	Sales Officer
jane.blow@company.com	20/02/2024	27/02/2024	Sales Officer
josh.blow@company.com	24/04/2024	1/05/2024	Data Analyst
jess.blow@company.com	9/03/2023	16/03/2023	Marketing Coordinator
josh.blow@company.com	24/12/2023	31/12/2023	Sales Officer
jess.blow@company.com	9/03/2023	16/03/2023	Marketing Coordinator
joe.blow@company.com	24/04/2024	1/05/2024	Data Analyst
jane.blow@company.com	24/04/2024	1/05/2024	Team Leader
josh.blow@company.com	24/12/2023	31/12/2023	Sales Officer
jess.blow@company.com	24/04/2024	1/05/2024	General Manager

[Auski]

Thank you both very much for your assistance here. Both options work here as a solution, however I am dealing with huge volumes of data (over 3million rows in each of the tables). Each of these calculations take over 20mins to complete.

Perhaps a calculated column isn't the best way to go(?) - performance wise. Interested to hear your thoughts!

[Ashish_Mathur]

Hi,

Write this calculated column formula in the Cases table

Column = CALCULATE(MAX('Org chart'[Employee Role]),FILTER('Org chart','Org chart'[Start of Week]<=EARLIER('Cases'[Created Date])&&'Org chart'[End of Week]>=EARLIER('Cases'[Created Date])&&'Org chart'[Email]=EARLIER('Cases'[Email])))

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

Employee Role CC =
SUMMARIZE (
    FILTER (
        SUMMARIZE (
            'org chart',
            'org chart'[Email],
            'org chart'[Start of Week],
            'org chart'[End of Week],
            'org chart'[Employee Role]
        ),
        'org chart'[Email] = 'cases'[Email]
            && 'org chart'[Start of Week] <= 'cases'[Created Date]
            && 'org chart'[End of Week] >= 'cases'[Created Date]
    ),
    'org chart'[Employee Role]
)