Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
LP280388
Resolver II
Resolver II

Is employee active within the given period

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 SlicerEndDate Slicer
01/01/202204/25/2022

 

IdStartDateEndDateActive?
1000104/14/201106/24/2011No
1000204/26/2022 No
1000304/04/2022 Yes
1000404/20/2022 Yes
1000504/24/2022 Yes
1000607/31/2011 Yes
1000711/10/201112/08/2011No
1000803/15/202203/15/2022Yes
1000911/24/202112/28/2021No
1001004/14/201104/26/2023Yes
1001103/15/2022 Yes
1001205/19/201205/19/2024Yes
1001304/14/2011 Yes
1001408/14/202012/18/2021No
1001503/15/2022 Yes
1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi,

 

Try this measure it shoul work

JamesFr06_0-1652358043934.png

 

PC2790
Community Champion
Community Champion

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

@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
Resolver II
Resolver II

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors