Visualisations with slicers determined by specific date range - active employees

[lozg]

Hi there,

I'm a pretty advanced Excel user, though very new to Power BI.
I've got some employee data and am trying to create an admin user overview dashboard to show the number of joiners, leavers, and currently active employees.

I've put a table, with an example of the raw data, below - which is updated each month (new hires added to the list and termination dates included for people who have left).

I've started creating the basic dashboard layout (see screenshot below).

All is ok, so far, however, I can't get things to work correcytly for the date ranges defined in the Date Slicer.

What I'm trying to achieve is to have the data update by any date range specificed to consider:

Card 1: Active Employees (Headcount) = Anyone with a hire date prior to the end of the date range specified in the Date slicer, less anyone who has a termination date prior to the start date of that same date range (this may be different from the 'Employee Status' column in the raw data, as that is just the static position today).

Card 2: Terminations In Period = Anyone who has a termination date in the date range specified in the Date slicer. 

Card 3: Terminations Year To Date = Anyone who has a termination date in the current year (using TODAY() to define the year).
Card 4: Terminations In Prior 12 Months = Anyone who has a termination date in the 12 months priod to today. 
Card 5: Joiners In Period = Anyone who has a hire date in the date range specified in the Date slicer.
Card 6 = Joiners Year To Date = Anyone who has a hire date in the current year (using TODAY() to define the year).
Card 7 = Joiners In Prior 12 Months = Anyone who has a hire date in the 12 months prior to today.

At the same time, I want the other slicers (Company, Gender, Country and Department) to work with Date slicer.

In summary, I would like the Date slicer to be the master filter - if a specific date range is specified, then all other filters apply to these dates and if no specific date filter is applied then it's all time.

I've spent about 6 hours yesterday looking at various different forum posts (many of them on here) and YouTube videos.
I've managed to take a few handy scripts and adapt them to perform part of the solution - however, I can't get the right measure to correctly filter the data as above.
I've got a Calendar with all the dates from the earliest to the latest date, for referencing. I just can't find the right combination of CALCULATE and DISTINCTCOUNT to make the above cards work correctly.

I would really greatly appreciate any help I can get on this.
Thank you so much!
LozG

Position ID	Name	Hire Date	Termination Date	Employee Status	Company	Department	Leave Reason	Gender	Country
1	Employee 1	14/12/2021		Active	Company A	Sales		Female	UK
2	Employee 2	23/04/2021		Active	Company B	Admin		Female	UK
3	Employee 3	15/07/2021		Active	Company C	Sales		Female	UK
4	Employee 4	04/11/2021	01/01/2022	Terminated	Company D	Sales	Voluntary	Female	UK
5	Employee 5	16/11/2021		Active	Company A	Markeing		Male	UK
6	Employee 6	17/11/2021		Active	Company A	Markeing		Male	UK
7	Employee 7	23/04/2021		Active	Company A	Markeing		Female	France
8	Employee 8	02/08/2021		Active	Company A	Markeing		Female	France
9	Employee 9	01/09/2021		Active	Company A	Markeing		Female	Spain
10	Employee 10	22/09/2021		Active	Company B	Finance		Female	Spain
11	Employee 11	06/10/2021	31/07/2022	Terminated	Company B	Finance		Female	Spain
12	Employee 12	21/10/2021		Active	Company B	Finance		Female	UK
13	Employee 13	22/10/2021		Active	Company B	Sales		Male	UK
14	Employee 14	27/10/2021		Active	Company A	Admin		Female	UK
15	Employee 15	13/11/2021		Active	Company A	Sales		Female	Italy
16	Employee 16	16/11/2021		Active	Company A	Sales		Female	Italy
17	Employee 17	30/11/2021		Active	Company C	Markeing		Female	Italy
18	Employee 18	05/10/2021		Active	Company C	Markeing		Female	Italy
19	Employee 19	01/12/2021		Active	Company C	Markeing		Female	UK
20	Employee 20	02/12/2021		Active	Company C	Markeing		Female	USA
21	Employee 21	15/08/2017	30/04/2022	Terminated	Company C	Markeing	Voluntary	Female	USA
22	Employee 22	09/10/2021		Active	Company A	Finance		Female	USA
23	Employee 23	13/12/2021		Active	Company D	Finance		Female	USA
24	Employee 24	13/09/2021		Active	Company A	Sales		Female	USA
25	Employee 25	04/10/2021		Active	Company A	Sales		Female	USA
26	Employee 26	06/12/2021		Active	Company D	Sales		Male	USA
27	Employee 27	18/12/2021		Active	Company D	Sales		Female	USA
28	Employee 28	26/10/2020		Active	Company D	Markeing		Female	USA
29	Employee 29	28/01/2019	31/12/2020	Terminated	Company D	Finance	Involuntary	Female	USA
30	Employee 30	02/09/2021		Active	Company A	Finance		Female	Italy
31	Employee 31	14/07/2021		Active	Company A	Finance		Female	UK
32	Employee 32	27/03/2017		Active	Company D	Admin		Female	France
33	Employee 33	01/10/2021		Active	Company C	Admin		Male	Spain
34	Employee 34	01/06/2020		Active	Company B	Sales		Female	Spain
35	Employee 35	12/11/2017		Active	Company A	Markeing		Female	USA

[vanessafvg · Accepted solution]

I have created one example for you see attached.

As you have more than one relationship on the date, you need a primary and inactive relationship, termination date is the inactive and you will see for the measure i use the userelationship function.    I used countrows(employee) to count but you can just as well use distinctcount(employeeid) or whatever it is.

[lozg]

Thank you so much for this, Vanessa!
Very helpful to guide in the right direction.
I've managed to use this as the basis to make some of the other cards.
The one I'm struggling to make is the 'Voluntary' terminations the current year.
I tried adpating the Measure to:

var mindate = DATE(YEAR(TODAY()),1,1)

var maxdate = DATE(YEAR(TODAY()),12,31)

var cnt  =  CALCULATE(COUNTROWS(Data),Filter(Data,Data[Leave Reason]="Voluntary") , DATESBETWEEN('Calendar'[Date], mindate, maxdate), USERELATIONSHIP('Calendar'[Date], Data[Termination Date]))

return cnt
This works perfectly for terminations without specifying the reason. However, when trying to specifiy the 'Voluntary' leavers in this way (specifically the Filter(Data,Data[Leave Reason]="Voluntary") element) it comes back blank.
Could you please help correct what I did wrong when trying to add a condition of 'Leave Reason' to the measure?

[vanessafvg]

will have a look at bit later and get back to you.