Filtering by the most recent date across 2 tables

[metcala]

Hi

I've spent a bit of time looking through the forum to try and find a solution that helps solve my problem to no avail!

Essentially I have a Role table:

Role ID

1001

1002

...

And I want to be able to add the current employee or vacancy.

Staff ID	Role ID	Start Date	End Date
1001	...	...	...
1002	...	...	...

Vacancy ID	Role ID	Start Date	End Date
1001	...	...	...
1002	...	...	...

My current thinking is to add a column onto the Role table looking up the last entry from each table. A slight complication is that some roles are pre filled in the future so would have to be up to the current date.

I've spent a while trying to get this working and any guidance as to whether this is a sensible approach or where/how to solve it would be very much appreciated!

Thanks in advance!

[metcala]

@daXtreme @amitchandak 

Thanks for your replies and apologies if my explanation wasn't clear!

I am looking to add a column showing either the current Staff ID or Vacancy ID. So for the example below Role 101 should show V002.

Staff ID	Role ID	Start Date	End Date
S001	101	1/8/2020	31/10/2022

Vacancy ID	Role ID	Start Date	End Date
V001	101	1/1/2020	31/7/2020
V002	101	1/11/2022

[v-easonf-msft · Accepted solution]

Hi， @metcala 

Please try formula like:

Vacancy ID = 
VAR _MAXDATE=CALCULATE(MAX('Table 2'[Start Date]),ALLEXCEPT('Table 2','Table 2'[Role ID]))
RETURN  CALCULATE(MAX('Table 2'[Vacancy ID]),FILTER(ALL('Table 2'),'Table 2'[Start Date]=_MAXDATE&&'Table 2'[Role ID]=MAX('Role table'[Role ID])))

Staff ID = 
VAR _MAXDATE=CALCULATE(MAX('Table 1'[Start Date]),ALLEXCEPT('Table 1','Table 1'[Role ID]))
RETURN  CALCULATE(MAX('Table 1'[Staff ID]),FILTER(ALL('Table 1'),'Table 1'[Start Date]=_MAXDATE&&'Table 1'[Role ID]=MAX('Role table'[Role ID])))

If it doesn‘t work, please share a sample file and expected outcome in excel for further research.

Best Regards,
Community Support Team _ Eason

[daXtreme]

Sorry, but this explanation is unclear.

[amitchandak]

@metcala , Can you share the expected output

These are ways to deal with range

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Tables way
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2