Using Slicer Value to Filter out Ranges of Data

[Anonymous]

Hi,

I am trying to set up an HR report where the user would select a report date (9/30,10/31) and get the active employees.

My problem is that I can capture the selected value of the slicer but I can't get it to compare to the termination dates so that if the report date was prior to the termination date it would still show as active.

My data looks like this...

Employee	Hire Date	Termination date
1	4/1/2020	8/1/2022
2	1/1/2019
3	6/1/2022	11/1/2022
4	12/1/2018

And the slicer would just be a dropdown

Report Date

6/1/22

7/1/22

8/1/22

9/1/22

10/1/22

11/1/22

12/1/22

Basically on a report date of 7/1/22 I would want to show 4 employees and then on 8/1 it would drop to 3.  

Any insight would be greatly appreciated.  

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1)  My test data is the same as yours.

(2) We can create a measure. 

Measure = IF(SELECTEDVALUE('Slicer Table'[Report Date])<MAX('Table'[Termination date]) || MAX('Table'[Termination date])==BLANK(),"active","no active")

(3)Set up filtering in the table visual.

(4) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[TomasAndersson]

Hi!
HR data is tricky since you have both hire and termination dates that needs to be taken into account. You need to create two relationships, one between Hire Date and your date table marked as active, and one between Termination Date and your date table markes inactive, plus some measures using USERELATIONSHIP()

There's a good article here with examples of measures that might be of use to you: HR Analytics - Active Employee, Hire and Terminati... - Microsoft Power BI Community

Hope this helps!