Forum Discussion
Finding the value based off date and ID
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:
- The business table which has the employee ID, Cost Centre and Date (AWD Date From)
- 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
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
Thank you so much it works perfectly 😄
2 Replies
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
Thank you so much it works perfectly 😄