Calculate active/inactive employees in a month

[mlsx4]

Hello everyone!

I'm looking for the proper way to compute the number of people which becomes inactive in a month.

Particularities: I have people from different working centers. I am using a Calendar Table. I have three slicers: center, year, month.

My data is something like:

ID Employee	Start date	End date	IsDown (T/F)	Center
1	29/10/2022	03/06/2023	True	02
2	02/06/2023	05/06/2023	True	02
3	22/12/2022	07/06/2023	True	02
4	15/10/2020	18/06/2023	True	02
5	14/07/2021		False	02
6	16/08/2022		False	03
7	15/02/2020	05/06/2023	True	03

Therefore, if I filter in a slicer by year "2023" and other by month "June" in center 2 I should get 4 people.

Up to now, I have implemented these measure:

Inactives=CALCULATE(COUNTROWS('Employees'),USERELATIONSHIP('Calendar'[Date],'Employees'[EndDate]))

However, it results in incorrect numbers.

Thanks in advance

[DoubleJ · Accepted solution]

Hi

Do you have an active relationship between your Dates and Employees using StartDate? My assumption is: When you use a year/month slicer, the center slicer only shows employees with a StartDate with the selected year and month.

You can use a separate Centers table to overcome this. Couldnt find a way to attach a file, so I hope the printscreen helps:

Hope I could help
JJ

[DoubleJ · Accepted solution]

Hi

Do you have an active relationship between your Dates and Employees using StartDate? My assumption is: When you use a year/month slicer, the center slicer only shows employees with a StartDate with the selected year and month.

You can use a separate Centers table to overcome this. Couldnt find a way to attach a file, so I hope the printscreen helps:

Hope I could help
JJ

[mlsx4 · Accepted solution]

I was using also a Center table, and the relationship was inactive for both Start and End Date. For inactive, it was my fault with filters... 😅

Thank you anyway!!

[pceglie]

Hi, in the visual (table I suppose) you have to add the measure you've created