Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
metcala
Helper III
Helper III

Filtering by the most recent date across 2 tables

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 IDRole IDStart DateEnd Date
1001.........
1002.........

 

Vacancy IDRole IDStart DateEnd 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!

1 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


 

View solution in original post

4 REPLIES 4
metcala
Helper III
Helper III

@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 IDRole IDStart DateEnd Date
S0011011/8/202031/10/2022

 

Vacancy IDRole IDStart DateEnd Date
V0011011/1/202031/7/2020
V0021011/11/2022 

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
Solution Sage
Solution Sage

Sorry, but this explanation is unclear.

amitchandak
Super User
Super User

@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

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.