Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Employee Count - based on Slicer Selection

Good Morning,

I have an interesting problem to solve - 

KPI is Count of Active Employees 

Slicers are Year, Quarter, Month

Data has employee id along with employee start date and employee end date.  

KPI needs to reflect the active employee count based on what the user selects in any of the slicers.  

 

If the user selects Year 2022 and Qtr Q1, and an employee leaves in Feb, the employee should be counted because employee was active in Q1.

 

I tried the following - but thats not the full solution -
Active Employee Count = Calculate(DISTINCTCOUNTNOBLANK('Emp-Data'[RACFID]),
            'Emp-Data'[start_date] <= Today(), 'Emp-Data'[end_date] >= Today())
)
 
Please suggest how to make it work with the Year, Qtr, and Month slicers.
 
Many thanks!!
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks Lucien.

    i created a range - min data and max date based on what the user selected in the Year, Qtr, Month slicers.

    Then i counted Active employees based on an overlap in the employee start and end date and in the selected range.

    It works!

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    Could you pls share a smaple data and expect output result.Remember to remove confidential data.

     

     

    Best Regards

    Lucien