Calculate age cluster dynamically in DAX - Power BI

Question

I have created a report for the HR department. One of the visuals aims to display the pyramid of ages of the employees by gender (we take cluster of 5 years, e.g. 20-25 for people between 20 and 25 years old).

To make it simple, data wise, I have a table with the list of employees, including their date of birth and many other fields, not relevant for this post. I added a calculated column with the age cluster based on the today’s date:

AgeCluster =

VAR AgeCalc=if(HR_DATA[Birthdate]=blank(),blank(),DATEDIFF(HR_DATA[Birthdate],today(),YEAR))

VAR Mult5=INT(AgeCalc/5)

RETURN

    if(isblank(AgeCalc),blank(),5*Mult5&"-"&5*(Mult5+1))

And I have a basic visual (tornado chart with the AgeCluster in Group, showing male and female)

Now my issue is that my report should be dynamic, so the user should be able to see the situation in the past or in the future... I have a calendar table (not linked to my HR_Data table), and a date slicer on my report's page. I need the age cluster to be recalculated.

I have tried a calculated table, but I can’t get it working properly. I have read various blog posts on similar issue, but still can't figure out how to solve it...

Any idea or tips much appreciated.

Thank you so much!

Answer 1

Dynamic filtering usually means in these situations that you need a fixed x-axis to work with. That x-axis is calculated outside the original table, such as a parameter table.

Assuming your data looks like this:

Table: Employees

EmployeeID	DOB
1	20 March 1977
2	05 December 1981
3	25 December 1951
4	20 December 1954
5	04 March 1980
6	24 July 1968
7	07 June 1984
8	01 October 1992
9	25 February 1999
10	02 November 1987

First, we need to create the Age Groups with their respective lower and top bands. This table uses a similar code when a parameter table is created.

Table: Buckets

Because you are using whole numbers, I think is best to calculate the TopBand by adding 4. In that case, you don't have repeating numbers. In other words, ranges shouldn't overlap (20-25 and 25-30)

Buckets =
SELECTCOLUMNS (
    GENERATESERIES ( 20, 80, 5 ),
    "Age Series",
        [Value] & " - " & [Value] + 4,
    "LowerBand", [Value],
    "TopBand", [Value] + 4
)

Also, we will need a Calendar Table to filter accordingly.

Table: CalendarHR

CalendarHR =
ADDCOLUMNS (
    CALENDAR ( MIN ( Employees[DOB] ), TODAY () ),
    "Year", YEAR ( [Date] )
)

With all that, you can create a calculation that can be filter dynamically.

DAX Measure:

EmployeesbyBracket = 
VAR _SelectedDate =
    MAX ( CalendarHR[Date] )
VAR _SelectedLowerBand =
    SELECTEDVALUE ( Buckets[LowerBand] )
VAR _SelectedTopBand =
    SELECTEDVALUE ( Buckets[TopBand] )
VAR EmployeesAge =
    ADDCOLUMNS (
        Employees,
        "Age",
            INT ( DATEDIFF ( [DOB], _SelectedDate, YEAR ) / 5 ) * 5
    )
RETURN
    COUNTROWS (
        FILTER (
            EmployeesAge,
            [Age] >= _SelectedLowerBand
                && [Age] <= _SelectedTopBand
        )
    )

Output

I've created a table visual with the Age Groups from the Buckets table.

Answer 2

Both a calculated column and a calculated table will be static and not achieve your desired objective. You need age to be calculated via a measure.

Create a disconnected table with your age buckets and then implement the dynamic segmentation pattern.