Demographic data at a slicer-selected point in time - how?

[FlorisMK]

I'm creating an HR dashboard, which includes, among other things, a slicer to select the reporting year. So far, this works perfectly.

I'm now at the point where I want to create the demography page: what was the composition of our workforce (for instance, in terms of age and gender) at a given point in time?

My goal is to have my users select a year in the slicer, and have the demography visuals show the composition of our workforce on 1 January of that year. If they unselect the slicer value, the current workforce composition should be shown.

Source data is a full list of employees, with birth dates, genders, and contract start and end dates.

So the slicer should result in selecting employees with start dates before, and end dates after of blank; and it should also result in calculating the ages of the employees at the selected dates.

Creating the query is easy, but I have no idea how to use slicer input to both select the dataset based on contract dates, and use the date to calculate ages for demography statistics.

Where do I start?

[Anonymous · Accepted solution]

Hi @FlorisMK 

For your question, here is the method I provided:

Here's some dummy data

“Employee”

Create a simple date table:

DateTable = CALENDAR (DATE(2023,1,1), DATE(2023,12,31)) 

Create a measure to Calculate Age.

Age = DATEDIFF(SELECTEDVALUE('Employee'[BirthDate]), DATE(SELECTEDVALUE('DateTable'[Date].[Year]), 1, 1), YEAR) 

To filter the employees based on the slicer selection, you can create a measure like this:

Active Employees = 
CALCULATE( 
COUNTROWS(Employee), 
FILTER( 
Employee, 
Employee[StartDate] <= DATE(SELECTEDVALUE('DateTable'[Date].[Year]), 1, 1) 
&& 
(ISBLANK(Employee[EndDate]) || Employee[EndDate] >= DATE(SELECTEDVALUE('DateTable'[Date].[Year]), 1, 1)) 
) 
) 

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FlorisMK]

Nice! Apologies, I was on vacation for three weeks. Glad to see that both the solution and its acceptance hava materialized in my absence 😄