Age Calculator in Power BI: DAX Formula to Calculate Age from DOB

An age calculator in Power BI can turn a date-of-birth field into a completed age in years with a short DAX formula. The important detail is to check whether this year’s birthday has already occurred. Simply subtracting birth years—or using DATEDIFF with the YEAR interval by itself—can return an age that is one year too high.

This guide shows how to add an age column, create a measure for report visuals, calculate age on a fixed cutoff date, and avoid common date-of-birth errors. The formula assumes the birth-date field contains valid dates rather than text.

1. Prepare the date-of-birth field

Load your people, employee, student or customer table into Power BI Desktop. In this example, the table is named People and the date-of-birth column is DateOfBirth. Replace those names with the ones in your own model.

• Select Data or Model view and choose the DateOfBirth column.
• Set its data type to Date if it represents a date without a time.
• Check for blank values, text dates, impossible dates and future dates before relying on the result.

Keeping DOB values as real dates helps DAX compare birthdays correctly. If dates arrive in ambiguous text formats such as 03/04/1998, confirm the source locale before converting them.

2. Create an age-in-years calculated column

Select the People table, choose New column, and enter this DAX expression. It returns blank for a missing or future date of birth and subtracts one year when the birthday has not happened yet this year.

Age (Years) =
VAR BirthDate = 'People'[DateOfBirth]
VAR AsOfDate = TODAY()
VAR BirthdayThisYear =
    DATE ( YEAR ( AsOfDate ), MONTH ( BirthDate ), DAY ( BirthDate ) )
RETURN
    IF (
        ISBLANK ( BirthDate ) || BirthDate > AsOfDate,
        BLANK (),
        YEAR ( AsOfDate ) - YEAR ( BirthDate )
            - IF ( BirthdayThisYear > AsOfDate, 1, 0 )
    )

The calculation starts with the difference between the two calendar years, then checks the month and day of the birthday in the current year. The adjustment means the result represents completed years, rather than just the number of January-to-December boundaries crossed.

Microsoft documents TODAY() as returning the current date and DATE() as creating a date from year, month and day values. See those references if your model uses special date handling.

3. Why DATEDIFF(YEAR) alone can be wrong

DAX DATEDIFF returns the number of interval boundaries between two dates. That is not always the same as a person’s age in completed years. For example, if the current date is 13 October 2026, someone born on 5 November 1998 is still 27—not 28—because their 2026 birthday has not occurred yet.

You can use DATEDIFF for elapsed intervals, but a completed-age calculation needs the birthday adjustment shown above. That distinction matters in eligibility dashboards, employee reports, student records and any chart that groups people by age bands.

4. Calculate age on an official cutoff date

For applications and eligibility checks, age is often measured on a specified cutoff date, not today. Copy the calculated-column pattern and replace TODAY() with a fixed date. For example, this returns age as of 13 October 2026; change the date to match the applicable notification or policy.

Age on Cutoff =
VAR BirthDate = 'People'[DateOfBirth]
VAR AsOfDate = DATE ( 2026, 10, 13 )
VAR BirthdayThisYear =
    DATE ( YEAR ( AsOfDate ), MONTH ( BirthDate ), DAY ( BirthDate ) )
RETURN
    IF (
        ISBLANK ( BirthDate ) || BirthDate > AsOfDate,
        BLANK (),
        YEAR ( AsOfDate ) - YEAR ( BirthDate )
            - IF ( BirthdayThisYear > AsOfDate, 1, 0 )
    )

Do not treat this example date as an official cutoff for any recruitment or admission. Use the date printed in the relevant notification, and check its category-specific relaxation rules separately. Leap-day birthdays may also be handled differently by particular laws or notices; the example formula follows the date produced by DAX DATE(), so confirm the required convention whenever eligibility depends on it.

5. Use a measure when the report should calculate on demand

A calculated column is handy when age needs to be used in filters, slicers, rows or columns. If you want an age value evaluated in a visual for the selected person, create a measure instead. For a table visual that has one person per row, the following measure uses SELECTEDVALUE to obtain that row’s DOB:

Age (Years) Measure =
VAR BirthDate = SELECTEDVALUE ( 'People'[DateOfBirth] )
VAR AsOfDate = TODAY()
VAR BirthdayThisYear =
    DATE ( YEAR ( AsOfDate ), MONTH ( BirthDate ), DAY ( BirthDate ) )
RETURN
    IF (
        ISBLANK ( BirthDate ) || BirthDate > AsOfDate,
        BLANK (),
        YEAR ( AsOfDate ) - YEAR ( BirthDate )
            - IF ( BirthdayThisYear > AsOfDate, 1, 0 )
    )

This measure is most straightforward when the visual’s filter context identifies a single DOB. If multiple people are selected without a row-level date, SELECTEDVALUE returns blank by design. Microsoft explains the differences between calculated columns and measures in its Power BI calculation options guide.

If you need the displayed age to change automatically each day, check your semantic-model refresh and service settings as well as the chosen calculation type. A stored calculated-column value does not tick forward by itself overnight; it must be recalculated through refresh.

6. Check the result with sample dates

Assuming an as-of date of 13 October 2026, the formula produces these illustrative results:

These are examples for checking the formula, not a substitute for a recruitment board’s published eligibility rules. If the visual returns a strange result, first inspect the DOB data type, date parsing, blank rows and any time component in the source field.

7. Common mistakes to avoid

• Subtracting years only: this ignores whether the birthday has passed.
• Using text instead of dates: convert and validate the column before calculating.
• Forgetting refresh behavior: a calculated column that uses TODAY() changes when the model recalculates, not continuously.
• Using today’s date for an application: change the as-of date to the exact cutoff printed in the official notice.
• Assuming a universal leap-day rule: for a legal or recruitment decision, follow the applicable notice or law rather than assuming every system uses the same anniversary convention.

Frequently asked questions

Is DATEDIFF enough to calculate a person’s age?

Not by itself. DAX DATEDIFF counts interval boundaries. For completed age in years, also check whether the birthday has occurred by the as-of date.

Should I use a calculated column or a measure?

Use a calculated column when you need age as a reusable row-level field for filters or age groups. Use a measure when the result belongs in a visual and should be evaluated for the current selection. Review refresh requirements if the date changes daily.

Can I calculate age on any date?

Yes. Replace TODAY() with a fixed DATE(year, month, day) value, or adapt the formula to use a validated cutoff-date field. For eligibility, use the official cutoff date from the relevant notification.

Where can I learn other DOB calculation methods?

For spreadsheet approaches, see our guides to the age calculator in Excel and age calculator in Google Sheets. For a quick individual calculation, use the site’s online age calculator.

Conclusion

A reliable Power BI age calculator needs more than a year subtraction. Keep DOB values as dates, adjust for whether the birthday has passed, and decide whether a stored column or an on-demand measure best fits the report. When the result is used for official eligibility, calculate against the prescribed cutoff date and confirm any special rules from the primary notice.