Finding the value based off date and ID

[Glenda]

Hi, 

Ok, I am sure this is not a hard one but I just can' get it so hoping someone can help me out. I need to find the cost centre a person worked for when they had an injury. I have 2 tables: 

1. The business table which has the employee ID, Cost Centre and Date (AWD Date From) 
2. Injury table with the employee ID and date of injury (DOI).

I need to find the Cost Centre at the time of the injury. 

Business Table

Employee ID	Cost Centre	AWD Date From
123456	422526	01/09/2007
123456	202526	06/07/2009
123456	101622	08/12/2014
123456	202526	08/01/2018
123456	200175	04/11/2019
987456	169235	20/12/2007
987456	169237	26/05/2008
987456	169207	22/12/2008
987456	169241	21/12/2009
987456	169209	25/10/2010
987456	169242	29/12/2014
987456	743336	18/06/2018
987456	743336	22/06/2020

Injury Table

Employee ID	DOI
123456	08/03/08
123456	03/09/11
123456	27/12/15
123456	06/07/20
987456	21/12/08
987456	22/12/08

The output I need is the injury table plus the cost where the employee was working at the date of injury like the table below. 

Employee ID	DOI	Cost Centre
123456	08/03/08	422526
123456	03/09/11	202526
123456	27/12/15	101622
123456	06/07/20	200175
987456	21/12/08	169237
987456	22/12/08	169207

Here is a link to my pbix with the data loaded - https://ufile.io/3oen7t5r

[mahoneypat · Accepted solution]

Thanks for providing a pbix file.  Here is an expression you can use in a new DAX column on your Injury Dates table, to get the Employees cost center at the time.

DOI Cost Center =
VAR __thisemployee = 'Injury dates'[Employee ID]
VAR __thisdate = 'Injury dates'[DOI]
VAR __CCdate =
    CALCULATE (
        MAX ( business[AWD Date From] ),
        business[AWD Date From] <= __thisdate,
        business[Employee ID] = __thisemployee
    )
RETURN
    CALCULATE (
        MIN ( business[Cost Centre] ),
        business[Employee ID] = __thisemployee,
        business[AWD Date From] = __CCdate
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mahoneypat · Accepted solution]

Thanks for providing a pbix file.  Here is an expression you can use in a new DAX column on your Injury Dates table, to get the Employees cost center at the time.

DOI Cost Center =
VAR __thisemployee = 'Injury dates'[Employee ID]
VAR __thisdate = 'Injury dates'[DOI]
VAR __CCdate =
    CALCULATE (
        MAX ( business[AWD Date From] ),
        business[AWD Date From] <= __thisdate,
        business[Employee ID] = __thisemployee
    )
RETURN
    CALCULATE (
        MIN ( business[Cost Centre] ),
        business[Employee ID] = __thisemployee,
        business[AWD Date From] = __CCdate
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Glenda · Accepted solution]

Thank you so much it works perfectly 😄