I am trying display a subset of employees employed on the 1st of every month over the last several years based on Hire and Separation Date. I can perform a count, but I also need to display a filter subset of the employees if I select a particular month.
Table 1 (Date Table) Table 2 (Employee Table)
01/01/2006 John Smith Hired Date: 01/05/2004 Separation Date: 09/21/2018
01/02/2006 John Doe Hired Date: 05/08/2008 Separation Date: null
01/03/2006 Jane Wright Hired Date: 09/14/2008 Separation Date: 11/15/2020
.... .......
.... .......
07/01/2021 ......
I wanted to create a matrix like this that shows active employee counts over the years and then be able to click on a cell to display the associated employees.
| | 2002 | 2003 | 2004 | 2005 |
| Jan | 12 | 14 | 20 | 21 |
| Feb | 10 | 13 | 18 | 21 |
| March | 8 | 14 | 17 | 23 |
I want to be able to click on the matrix cell and for January 2002 and see a list of the 12 employees on working for the company on January 1st. The list of employees would change and filter depending on the cell context that was clicked. These two tables would not be related because you are having to write DAX to figure out which employees were still employed given the date and time table.
Any suggestions would be appreciated. I can get a correct count by using variables and a calculate formula but I cannot filter or display a list of employees based on the count and matrix context.
