Is employee active within the given period

[LP280388]

Hi Team,

I need a help in filtering a visual to show only the active users between the From and To Date.  Below is how my data looks like. I have slicer from StartDate and EndDate. 

When a user selects the date from these Slicers, the table visual should only show the employees who were active during this time. 

Im trying to achieve the last column marked in Red below. 
the criteria would be,
1. when a user has startdate and end date within the selected period

2. when a user has startdate or end date within the selected period

3. when a user has startdate before the startdateslicer and enddate is blank

In the above scenarios, the condition turns to true and I like all the users in the table visuals where the condition is true.

Kinldy help on achieving this please.

StartDate Slicer	EndDate Slicer
01/01/2022	04/25/2022

Id	StartDate	EndDate	Active?
10001	04/14/2011	06/24/2011	No
10002	04/26/2022		No
10003	04/04/2022		Yes
10004	04/20/2022		Yes
10005	04/24/2022		Yes
10006	07/31/2011		Yes
10007	11/10/2011	12/08/2011	No
10008	03/15/2022	03/15/2022	Yes
10009	11/24/2021	12/28/2021	No
10010	04/14/2011	04/26/2023	Yes
10011	03/15/2022		Yes
10012	05/19/2012	05/19/2024	Yes
10013	04/14/2011		Yes
10014	08/14/2020	12/18/2021	No
10015	03/15/2022		Yes

[Anonymous]

Hi,

Try this measure it shoul work

[PC2790 · Accepted solution]

See if the below measure works:

Measure = 
VAR FirstDay = CALCULATE(
        MIN('DateTable'[Date]),
        ALLSELECTED('DateTable'[Date])
    )
    VAR LastDay = CALCULATE(
    MAX('DateTable'[Date]),
    ALLSELECTED('DateTable'[Date])
)
Return 
SWITCH(TRUE(),
 FirstDay < MAX(MainTable[StartDate]) && Max(MainTable[EndDate])<LastDay,"Yes",
 FirstDay < MAX(MainTable[StartDate]) && Max(MainTable[EndDate]) = BLANK(),"Yes",
 "No")

[LP280388]

@PC2790 Thank you. with a little tweak I was able to get to the result withe approach you have provided. Thank you very much

[LP280388]

@amitchandak Hi Amit thank you very much for the link. 

Ratherthan count, Im looking for a column Indicator to show the active users for the selected period.

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

  How can I modify this to  show as a column instead of measure? Please help.

[amitchandak]

@LP280388 , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...