Filtering date ranges

[jacques_412]

I have a table of employees with a column for occupancy start date and a column for occupancy end date. If someone gets promoted or moves to a new department, a new row is created with a new start date and end date, and the previous row gets an end date. Thus, people who have been in the company for a while can have multiple rows. If someone is still working at the company in a particular position, there is no end date. Thus, this table provides a history of who was in the company and where at different times.

I want to add a filter where I can select date ranges, and see exactly who is in the company and where. So if I select 01 January until 28 January, I will see everyone who has an occupancy during that period, even if it was only for a day.

How would I do this?

[jacques_412]

The problem we have is that there can be multiple rows for each person as they move around the organization, while in your solution the start date is when they joined, and end date is when they left.

[amitchandak]

@jacques_412 , I need to know the start date when they move around and what you want to count. Check this one, I made a few changes and used a distinct count. File attached, multiple records of employees created

Active should be ok with this example. for hire we might have adjust with hire date for a continuous tenure

[amitchandak]

@jacques_412 , Refer if this approach can help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

You can refer to the file attached after your signature

[jacques_412]

Thanks for this. It is not quite what I am looking for. Someone in the comment section of your Youtube video asked the same question a year ago, but there is no answer.