Calculating age when date filter changes

Power BI
1

Hi everyone,

I have a list of employees and a date slicer on my report (Date From & Date To). When I change the “Date To” I need to calculate the following:

  1. The age of each employees relative to the “Date To”, for example: “Date To” - BirthDate
  2. The no. of years of service for each employee, relative to the “Date To”, for example, “Date To” - “Date Joined”
  3. The Years of Service Grouping for each employee, relative to the “Date To” e.g. 1-5, 6-10, etc.

The problem I’m having is that, when I change the “Date To” in the date slicer, the Age and Years of Service do not change.

What would be the best way to iterate through the Employees table and update the above 3 elements please?

Thanks in advance.

Kind regards,
Yogesh

2

I don’t know if it’s the way to calculate the Age, but, the following measure seems to work:

Age = AVERAGEX(
    'Employees', 
    IF(
        [Date To] >= TODAY(),
        DATEDIFF( 'Employees'[BirthDate] , TODAY() , YEAR ), 
        DATEDIFF( 'Employees'[BirthDate] , [Date To] , YEAR )
    )
)
3

Looks pretty good to me.

Logically you are iterating through every row of the employee table.

At every row you are running you IF statement and saving each individual calculated result at every row into memory.

Once the iterations have completed you’re averaging them all via the AVERAGEX.

It looks efficient to me.

Chrs

1 Like
4

Hi Sam,

Many thanks for your reply.

Kind regards,
Yogesh