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