topic Re: Filtering by the most recent date across 2 tables in DAX Commands and Tips

Filtering by the most recent date across 2 tables

metcala — Fri, 04 Nov 2022 14:25:51 GMT

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!

Re: Filtering by the most recent date across 2 tables

amitchandak — Sat, 05 Nov 2022 02:26:54 GMT

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-trend/ba-p/882970

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-or-end-date/ba-p/1503785

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

Re: Filtering by the most recent date across 2 tables

daXtreme — Sat, 05 Nov 2022 09:11:49 GMT

Sorry, but this explanation is unclear.

Re: Filtering by the most recent date across 2 tables

metcala — Sat, 05 Nov 2022 16:28:36 GMT

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

Re: Filtering by the most recent date across 2 tables

v-easonf-msft — Thu, 10 Nov 2022 09:14:36 GMT

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