Single date filter to count the values between two date ranges

[rajaurora]

Hello
I have a requirement where a single date filter should be able to filter the date range in a table where the date from calendar table as selected value should check the source table and count employees where the selected date filter from the slicer is Less than equal to Adj Hire date and selected date is GT the term date in the source table

example: If the user select 12/31/2020 the selected value count all the employees who have adj hire date less than equal to 12/31/2020 and all employees who have terminated date Greater than 12/31/2020
Count according to the application is 5073 (for validation)

Any other suggestions also welcome

Thank you for the help

[v-yalanwu-msft · Accepted solution]

Hi, @rajaurora ;

According to your description, i create a simple data to count. and create a measure as follows:

count = CALCULATE(COUNT([Employee Id ]),FILTER(ALL(Employee),[Start Date]<=MAX('calendar'[Date])&&[End Date]>MAX('calendar'[Date])))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@rajaurora , my HR blog should help you that

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

HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4