cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## List of active employees on a date

I have a list of employees with dates of admission and dismissal. I need to show a list of active employees on a date that the user will choose. Could you help me how to solve?

1 ACCEPTED SOLUTION
Community Support

According to your needs, you want to get a list of employees in the Active status by filtering different dates. I did the following test, which can be used as a reference: first obtain the maximum and minimum values of the date in the slicer, and then judge in the if condition. The mark that meets the condition is “Active”, and the one that is not satisfied is “Terminated”. Then put the measure into Filters to filter out the list of Active employees.

Cur_Status =
IF (
MAX ( [Termination Date] ) = BLANK (),
IF ( MAX ( [Hire Date] ) >= [First_Slicer_Date], "Active","Terminated" ),
IF (
MAX ( [Termination Date] ) <= [Second_Slicer_Date]
&& MAX ( [Hire Date] ) >= [First_Slicer_Date],
"Acitve",
"Terminated"
)
)

Here is the sample pbix file.

If the problem is still not resolved, please let me know immediately, looking forward to your reply.

Best Regards,
Henry

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

4 REPLIES 4
Community Support

According to your needs, you want to get a list of employees in the Active status by filtering different dates. I did the following test, which can be used as a reference: first obtain the maximum and minimum values of the date in the slicer, and then judge in the if condition. The mark that meets the condition is “Active”, and the one that is not satisfied is “Terminated”. Then put the measure into Filters to filter out the list of Active employees.

Cur_Status =
IF (
MAX ( [Termination Date] ) = BLANK (),
IF ( MAX ( [Hire Date] ) >= [First_Slicer_Date], "Active","Terminated" ),
IF (
MAX ( [Termination Date] ) <= [Second_Slicer_Date]
&& MAX ( [Hire Date] ) >= [First_Slicer_Date],
"Acitve",
"Terminated"
)
)

Here is the sample pbix file.

If the problem is still not resolved, please let me know immediately, looking forward to your reply.

Best Regards,
Henry

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

Solution Sage

In the report tab, apply page level filter on the "Termination Date" field as "Is Blank", now you will have only the active employees on this report page.

Hire Date can be slicer for users to select and you can create list of active employee.

Hope above is making sense, let me know if this is what you are looking for, if not please provide more details.

Helper I

Thanks for the help, but I need a list of active users on any date.
For example, if an employee was fired on 7/20/2017 and I choose the date of 7/19/2017, that employee should appear on the list because he was active on that date.

Solution Sage

I think there can be multiple solution to this, try below:

Assuming for the Date you have a separate table which is coming to the slicer/filter for the selection, and also assuming that there will be single selection on the date.

--Create a measure

Criteria =
If(ISBLANK(MAX(ListOfEmployees[Termination Date])),

if(
VALUES('Date'[Date])>=Max(ListOfEmployees[Hire Date]) ,
"Active","Terminated")
,
if(
VALUES('Date'[Date])>=Max(ListOfEmployees[Hire Date]) && VALUES('Date'[Date])<Max(ListOfEmployees[Termination Date]) ,
"Active","Terminated")
)

--Drag the Payroll employee and other details to the table visual.
--Apply page level or visual level filter on above measure, "is Active"

This should work.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors