List of active employees on a date

[JorgePereira]

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?

[v-henryk-mstf · Accepted solution]

Hi @JorgePereira ,

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.

[mhossain]

@JorgePereira 

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.

[JorgePereira]

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.

[mhossain]

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.