cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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?

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Regular Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.