Employee Count - based on Slicer Selection

[Anonymous]

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

[Anonymous]

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]

Hi @Anonymous ,

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

Best Regards

Lucien