Forum Discussion
Visualisations with slicers determined by specific date range - active employees
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 |
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.
3 Replies
- vanessafvg
Community Champion
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.
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.
