How to calculate age from date of birth in Power BI (Two Solutions)

In this tutorial, we show you two easy methods for calculating ages from dates of birth in Power BI:

Calculating age from date of birth using Power Query

Firstly we will show you how to calculate age from date of birth in Power Query.

Here are some example dates of birth:

If you want to keep the original date of birth column, we suggest you duplicate it, rename it to ‘Age’ and then perform the following on the ‘Age’ column.

Navigate to the Transform section on the top ribbon.

Next, select the drop-down list on the ‘Date’ button and choose ‘Age’.

This will use the date of birth to give an age in days.

Then navigate to the ‘Duration’ button on the top ribbon and select ‘Total Years’.

This will then return the age in years for each date of birth.

The below shows the results in the ‘Age’ column. We have also added a ‘Today’ column with today’s date for reference.

Now, as you may have noticed, there is an error with this method in Power Query.

Simply selecting ‘Age’ and ‘Total’ years from the ribbon won’t give you a 100% accurate age from date of birth.

Look at the last two rows in the table. A person born on 5th January 1975 would be 48 on 7th January 2023 (today’s date) and a person born on 9th January 1975 would be 47 on 7th January 2023.

However, the column has returned the age of 48 for both rows.

This is because the age is calculated on the basis of each year being 365 days. This is what causes the miscalculation since leap years contain an extra day.

To achieve a more accurate age from the date of birth, you can change the formula in the applied step that first calculates the age.

Simply change 365 to 365.25. See the code below for reference.

= Table.TransformColumns(#"Calculated Age",{{"Age", each Duration.TotalDays(_) / 365.25, type number}})

After editing the code to include 365.25, these are the new results we get for the age:

Next, to get a whole number, navigate to the top ribbon again and go to ‘Rounding’ then select ‘Round Down’.

This will round the dates down to the nearest whole number.

See the results below.

Calculating age from date of birth using DAX

Another way to calculate age from date of birth in Power BI is to use the YEARFRAC formula in your table.

This will produce the number of years from the difference between two dates, i.e. the date of birth and today’s date.

You need to have a date of birth column and also a column with today’s date to reference in the formula.

There is also a parameter where you can choose what basis the formula uses. These are as follows:

0: US (NASD) 30/360 (Default value)

1: Actual/actual

2: Actual/360

3: Actual/365

4: European 30/360

To calculate age from date of birth we want to use the basis ‘1’.

This counts the actual amount of days between the two periods, rather than using 365 days per year like in Power Query.

We will also want to combine this formula with the ROUNDDOWN function to round down the years, like in Power Query also.

Here is the formula we used in our table:

DAX Age = ROUNDDOWN(YEARFRAC('Table (3)'[Date of Birth],'Table (3)'[Today],1),0)

Here are the results after the column was added:

You can use either of these methods (Power Query or DAX) to calculate age from date of birth in Power BI, however, we recommend using YEARFRAC in DAX for a more accurate calculation.