How to add filter for worker status - Active, Exited and Pending

[Anonymous]

Hello,

I would like to add a filter in the Power BI report to select whether the employee is Active, Pending, or Exited.

Below is my data.

Employee name	Employment start date	Employment end date
John	01/01/2021	Never
Adam	01/01/2010	01/01/2021
Celina	12/12/2021	Never
Jennifer	05/07/2021	Never

How can I achieve this in Power BI?

For the active employees:  The employment start and end dates are between the current date.

employment start date<=  current date && employment end date >= current date

For the Exited employees:  This should be past employees. The employment end date should be less than the current date

For the Pending employees: These should be future employees which means the employment start date is greater than the current date.

[smpa01 · Accepted solution]

@Anonymous  you need a calculated column for slicer as following

Column = switch ( TRUE(), Emp[Employment end date]="Never", "Active", Emp[Employment start date]>=TODAY(),"Pending", CONVERT(Emp[Employment end date],DATETIME)<TODAY(),"Exited")

[PaulDBrown]

@smpa01 

You need to swap the order of the first and second clause in the SWITCH Statement.

[smpa01]

Thanks @PaulDBrown ; please give the above a try @Anonymous 

[smpa01]

@Anonymous  "I would like to add a filter in the Power BI report to select whether the employee is Active, Pending, or Exited." - based on what logic?

[Anonymous]

I edited the post and added logic.

[Anonymous]

Thanks the solution works.