How to count employees of different types of seniority

[VHL]

Hello. 

I would like to ask for help with something, probably a measure or a set of measures, but I will take anything, that will allow me to count (month by month) how many employees have been with us for less than 6 months, for 6-18 months, for 18-36 months and more than 36 months, at any month during the last 3 years.

I have a Calendar table, which is a date table, consisting od Date, Year, Month.

Then I have a table of employees consisting of ID, Name, Start_date, End_date, where Start_date is the date employee started on their position and End_date is the date they left this position. If the employee is still hired and working, End_date is left empty. One employee can be in this table multiple times, for example one record (row) dated from first starting with company to leaving for maternity leave, then second record (row) dated from returning from maternity leave to now. Example of data:

ID	Name	Start_date	End_date
1	A	1.6.2023
2	B	1.7.2017	29.2.2020
2	B	1.3.2021	31.10.2022
4	C	1.12.2017	31.1.2021
4	C	1.2.2021
5	D	1.3.2002
6	E	20.1.2020	31.12.2022
6	E	1.1.2023	31.8.2023
7	F	11.10.2017	29.2.2020
7	F	1.3.2020	31.3.2022
8	G	15.3.2014	30.11.2015
8	G	1.12.2015	14.4.2019
8	G	15.4.2019	28.2.2020
8	G	1.1.2023
9	H	1.1.2023	29.3.2023
10	I	15.1.2002
11	J	17.11.2014
12	K	1.1.2023	17.3.2023
13	L	15.10.2017	29.2.2020
13	L	1.3.2020	30.11.2020
14	M	19.9.2012
15	N	1.10.2005
16	O	6.6.2016	31.1.2021
16	O	1.2.2021
17	P	1.12.2018	29.2.2020

I have multiple interconnected tables in my model, so the Calendar table and Employee table aren't in a relationship and the only relationship I can create between them is inactive (and that seemed to have its problems in measures I tried, as when conecting Date + Start_date and Date + End_date, it was saying there are ambiguous paths between tables).

The result I need is a stacked area chart (or something similar) + a table showing the number of employees in each category, like this (rok = year in my language):

None of the measures I tried really worked and I tried about 7-8 different ones, and I spent 2 weeks trying to make them work. So apologies, but I am not posting any here - I have no idea which version might be closest to correct.

Does anyone know how to do this? 

Thank you very much for help.

[TobyNye]

I created a data model based on the values you put in your post:

I have kept these tables unrelated as you explained:

I created a measure for each grouping to create the kind of visual that you wanted:

This is the general structure that all of the measures follow:

6 - 17 Months = CALCULATE(DISTINCTCOUNT(Data[ID]), FILTER(Data,

                                                    (SELECTEDVALUE(dimDate[Year]) - YEAR(Data[Start_date])) * 12 + SELECTEDVALUE(dimDate[Month Num]) - MONTH(Data[Start_date]) >= 6

                                                    && (SELECTEDVALUE(dimDate[Year]) - YEAR(Data[Start_date])) * 12 + SELECTEDVALUE(dimDate[Month Num]) - MONTH(Data[Start_date]) <= 17

                                                    && YEAR(Data[Start_date]) <= SELECTEDVALUE(dimDate[Year])

                                                    && MONTH(Data[Start_date]) <= SELECTEDVALUE(dimDate[Month Num])

                                                    && ((YEAR(Data[End_date]) >= SELECTEDVALUE(dimDate[Year])

                                                    && MONTH(Data[End_date]) > SELECTEDVALUE(dimDate[Month Num])) || ISBLANK(Data[End_date]))

)

) + IF(SELECTEDVALUE(dimDate[Year]) >= 2020, 0)

You can change the >= and <= signs as fits your use case, the setup I have put in is going from 6-17 months such that the next grouping can be 18-35 then 36+. The first two arguments in the filter clause are effefctively just a date diff but using only year and month as that is as far as you wanted to drill down on your graph (therefore they would be the only parts of the date with a value at that level). You can also change the numbers as you please if you want the bandings to be a bit different, this is just what I thought you wanted. The + IF(SELECTEDVALUE(dimDate[Year]) >= 2020, 0) is not entirely necessary, it just means that the value will be blank if the date from the date table is outside of the selected range and replace blanks with a 0 inside the range so it looks nice, you can remove that entirely if you want, the graph will just show blanks instead of 0's. Note that it won't work if you don't have some kind of date filter affecting the visuals that you use it in. 

The rest of the measures:

0 - 5 Months = CALCULATE(DISTINCTCOUNT(Data[ID]), FILTER(Data,

                                                    (SELECTEDVALUE(dimDate[Year]) - YEAR(Data[Start_date])) * 12 + SELECTEDVALUE(dimDate[Month Num]) - MONTH(Data[Start_date]) < 6

                                                    && YEAR(Data[Start_date]) <= SELECTEDVALUE(dimDate[Year])

                                                    && MONTH(Data[Start_date]) <= SELECTEDVALUE(dimDate[Month Num])

                                                    && ((YEAR(Data[End_date]) >= SELECTEDVALUE(dimDate[Year])

                                                    && MONTH(Data[End_date]) > SELECTEDVALUE(dimDate[Month Num])) || ISBLANK(Data[End_date]))

)

) + IF(SELECTEDVALUE(dimDate[Year]) >= 2020, 0)

18 - 35 Months = CALCULATE(DISTINCTCOUNT(Data[ID]), FILTER(Data,

                                                    (SELECTEDVALUE(dimDate[Year]) - YEAR(Data[Start_date])) * 12 + SELECTEDVALUE(dimDate[Month Num]) - MONTH(Data[Start_date]) >= 18

                                                    && (SELECTEDVALUE(dimDate[Year]) - YEAR(Data[Start_date])) * 12 + SELECTEDVALUE(dimDate[Month Num]) - MONTH(Data[Start_date]) <= 35

                                                    && YEAR(Data[Start_date]) <= SELECTEDVALUE(dimDate[Year])

                                                    && MONTH(Data[Start_date]) <= SELECTEDVALUE(dimDate[Month Num])

                                                    && ((YEAR(Data[End_date]) >= SELECTEDVALUE(dimDate[Year])

                                                    && MONTH(Data[End_date]) > SELECTEDVALUE(dimDate[Month Num])) || ISBLANK(Data[End_date]))

)

) + IF(SELECTEDVALUE(dimDate[Year]) >= 2020, 0)

36+ Months = CALCULATE(DISTINCTCOUNT(Data[ID]), FILTER(Data,

                                                    (SELECTEDVALUE(dimDate[Year]) - YEAR(Data[Start_date])) * 12 + SELECTEDVALUE(dimDate[Month Num]) - MONTH(Data[Start_date]) >= 36

                                                    && YEAR(Data[Start_date]) <= SELECTEDVALUE(dimDate[Year])

                                                    && MONTH(Data[Start_date]) <= SELECTEDVALUE(dimDate[Month Num])

                                                    && ((YEAR(Data[End_date]) >= SELECTEDVALUE(dimDate[Year])

                                                    && MONTH(Data[End_date]) > SELECTEDVALUE(dimDate[Month Num])) || ISBLANK(Data[End_date]))

)

) + IF(SELECTEDVALUE(dimDate[Year]) >= 2020, 0)

6 - 17 Months = CALCULATE(DISTINCTCOUNT(Data[ID]), FILTER(Data,

                                                    (SELECTEDVALUE(dimDate[Year]) - YEAR(Data[Start_date])) * 12 + SELECTEDVALUE(dimDate[Month Num]) - MONTH(Data[Start_date]) >= 6

                                                    && (SELECTEDVALUE(dimDate[Year]) - YEAR(Data[Start_date])) * 12 + SELECTEDVALUE(dimDate[Month Num]) - MONTH(Data[Start_date]) <= 17

                                                    && YEAR(Data[Start_date]) <= SELECTEDVALUE(dimDate[Year])

                                                    && MONTH(Data[Start_date]) <= SELECTEDVALUE(dimDate[Month Num])

                                                    && ((YEAR(Data[End_date]) >= SELECTEDVALUE(dimDate[Year])

                                                    && MONTH(Data[End_date]) > SELECTEDVALUE(dimDate[Month Num])) || ISBLANK(Data[End_date]))

)

) + IF(SELECTEDVALUE(dimDate[Year]) >= 2020, 0)

Hope this helps, let me know if you have any further questions or issues.

[VHL]

@TobyNye Thank you for trying. I checked your measures, but from what I see, they don't return correct numbers. They seem to have the same problem mine had when I tried - they don't sum peoples months here by ID. 

On the sample I offered, I would expect in current month to have overall 9 people:

0 - 5 Months:     0

6 - 17 Month:     1  (A)

18 - 35 Months:  0

36+ Months:       8 (C, D, G, I, J, M, N, O)

Any idea how to do that?

Otherwise what you did there is great 🙂

[VHL · Accepted solution]

In the end I couldn't make this work through measures alone. I solved this problem by creating a table "SENIORITY" through SQL select with 3 columns (orange): 

1. Date - end of month for every month during the last 3 years (this was enough for the chart and table I need)

2. ID of employee

3. value 1 if this employee was working for us on that date or 0 if they weren't

To this table I appended, for each employee ID (if it appeared in 3 rows then 3 times per that ID), with the date of 3 years minus 1 month, count of all the months they were working for us before the oldest date in the table (green):

Date	ID	Presence
31.1.2021	1	13
31.1.2021	2	18
31.1.2021	2	75
31.1.2021	3	221
31.1.2021	4	28
31.1.2021	5	101
31.1.2021	5	14
31.1.2021	5	19
31.1.2021	6	184
28.2.2021	1	0
28.2.2021	2	1
28.2.2021	3	1
28.2.2021	4	1
28.2.2021	5	1
28.2.2021	6	1
31.3.2021	1	0
31.3.2021	2	0
31.3.2021	3	1
31.3.2021	4	1
31.3.2021	5	1
31.3.2021	6	1
30.4.2021	...	...

This table is refreshed daily. With it, I managed to get my numbers through simple measures:

Sum of Presence = 

var _currentdate = SELECTEDVALUE('SENIORITY'[Date])

RETURN

CALCULATE(SUM('SENIORITY'[Presence]), ALL('SENIORITY'), VALUES('SENIORITY'[ID]), 'SENIORITY'[Date] <= _currentdate)

0 - 0,5 years =

VAR _result = CALCULATE(DISTINCTCOUNT('SENIORITY'[ID]),

          FILTER('SENIORITY', 'SENIORITY'[Sum of Presence] <= 6  && 'SENIORITY'[Presence] <> 0)

          )

RETURN

IF(NOT(ISBLANK(_result)), _result, 0)

0,51 - 1,5 years =

VAR _result = CALCULATE(DISTINCTCOUNT('SENIORITY'[ID]),

          FILTER('SENIORITY', 'SENIORITY'[Sum of Presence] <= 18 && 'SENIORITY'[Sum of Presence] > 6  && 'SENIORITY'[Presence] <> 0)

         )

RETURN

IF(NOT(ISBLANK(_result)), _result, 0)

1,51 - 3 years =

VAR _result = CALCULATE(DISTINCTCOUNT('SENIORITY'[ID]),

          FILTER('SENIORITY', 'SENIORITY'[Sum of Presence] <= 36 && 'SENIORITY'[Sum of Presence] > 18  && 'SENIORITY'[Presence] <> 0)

         )

RETURN

IF(NOT(ISBLANK(_result)), _result, 0)

3+ years =

VAR _result = CALCULATE(DISTINCTCOUNT('SENIORITY'[ID]),

          FILTER('SENIORITY', 'SENIORITY'[Sum of Presence] > 36 && 'SENIORITY'[Presence] <> 0)

         )

RETURN

IF(NOT(ISBLANK(_result)), _result, 0)