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
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.