Slicer-filtered population piramid from employee birth days

[FlorisMK]

Based on a table of employee records, including their birth dates and contract info, I need to create a population piramid. But there's an added challenge: the piramid needs to be sliceable to user-selected years. In other words, I want to show today's population piramid, but also the population piramid of, say, 2021 when my user selects that year.

Here's what I have in place:

• A People table with employee records, their birth dates and genders, and contract start and end dates.
• A Calendar table with all dates.
• A year slicer based on the calendar table where users can select a year (already in use to slice other visuals.

What I want:

• A population piramid based on the People data (10-year divisions).
• The visual responding to the selected year by showing the population on Jan 1st of that year.
• With no year selected, the population piramid for TODAY()

What I'm using as basis for the population piramid:

• Two mirrored bar charts to show F and M sides of the population piramid (to be refined visually).

What I tried first:

• Colums for the information I need:
  • Age based on birthdate and SELECTEDVALUE from the slicer.
  • AgeBracket based on Age.
  • Employed (0/1) also based on the SELECTEDVALUE, and the contract dates.
• Used these columns in the bar chart. Worked like a charm, but did nothing with the selected slicer value. Learned since that columns can't use slicer values, so SELECTEDVALUE always used my default of TODAY(). So I got a present-day population piramid, but could not show previous years.

What I've changed now:

• Age, AgeBracket, and Employed as measures.

But when I tried to use these in the bar chart, it stopped at the AgeBracket. Bar chart does not accept a measure as Y axis field.

So now I'm stumped. Using columns gets me what I want, but cannot be based on slicer value. Using measures can, but cannot be used in the bar chart axis.

How can I achieve what I want? Where is my thinking going wrong?

[speedramps · Accepted solution]

Hi @FlorisMK 

Click here to download a solution from onedrive

Click here 

Please click the thumbs up because I have spent a lot of time on this to try help you.

Then click accept solution if it works.

You can accept more than one solution.

How it works ....

Build detached tables with no relationships

This is the calendar table (first few records)

This is the age band table

Ths is your employee table

Add a slicer using the calendar year with single slection

User a measure to get the age using the start of the slicer year

Age = 
DATEDIFF(
MIN(Employees[DOB]),  MIN('Calendar'[Date]), YEAR)

Count the number of employees in the band 

Employess in band = 
var bandstart = MIN('Age bands'[Start]) 
var bandend = MIN('Age bands'[End]) 
RETURN
SUMX(Employees, IF( [Age] >= bandstart && [Age] <= bandend,1) )

Draw your graph

test the graph

Click here to download a solution from onedrive

Click here 

[speedramps]

Thank you @FlorisMK 

[FlorisMK]

Hi @speedramps , thanks a bundle! This is great. I've combined it with comparing the selected date to the contract dates to decide what value to sum, and now I have a working solution! I'll take a look at your gender solution as well, but this deserves an Accept as Solution already.

[speedramps · Accepted solution]

This version has the gender split.

Please click thumbs up and accept solution

Click here to download solution from Onedrive 

[speedramps · Accepted solution]

Hi @FlorisMK 

Click here to download a solution from onedrive

Click here 

Please click the thumbs up because I have spent a lot of time on this to try help you.

Then click accept solution if it works.

You can accept more than one solution.

How it works ....

Build detached tables with no relationships

This is the calendar table (first few records)

This is the age band table

Ths is your employee table

Add a slicer using the calendar year with single slection

User a measure to get the age using the start of the slicer year

Age = 
DATEDIFF(
MIN(Employees[DOB]),  MIN('Calendar'[Date]), YEAR)

Count the number of employees in the band 

Employess in band = 
var bandstart = MIN('Age bands'[Start]) 
var bandend = MIN('Age bands'[End]) 
RETURN
SUMX(Employees, IF( [Age] >= bandstart && [Age] <= bandend,1) )

Draw your graph

test the graph

Click here to download a solution from onedrive

Click here