Skip to main content
cancel
Showing results for 
Search instead 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

Reply
JorgePereira
Helper I
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?

employee.PNG

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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"
    )
)

v-henryk-mstf_0-1611279708217.png

v-henryk-mstf_1-1611279734466.png

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.

View solution in original post

4 REPLIES 4
v-henryk-mstf
Community Support
Community Support

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"
    )
)

v-henryk-mstf_0-1611279708217.png

v-henryk-mstf_1-1611279734466.png

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
Solution Sage
Solution Sage

@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.

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

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