How Do You Calculate Age in Power BI DAX?

How Do You Calculate Age in Power BI DAX?

Age looks like the easiest calculation in any model. You have a date of birth in one column, today's date is a function call away, and the gap between them is the answer. Most people write it in about fifteen seconds.

Then someone in the business notices that a customer who turns 40 next week is already being counted in the 40-44 bracket, and the segmentation report that drove a quarter of marketing spend turns out to have been quietly wrong at every band boundary.

This is one of those calculations where the obvious formula and the correct formula are different formulas. The obvious one is DATEDIFF with a YEAR interval. It compiles, it returns plausible numbers, and it is not measuring what you think it is measuring.

This guide covers what to write instead, why the simple version drifts, and the handful of modelling decisions that matter more than the formula itself.

How do you calculate age in Power BI DAX? The direct answer

The pattern that holds up is integer date arithmetic rather than a date-difference function. Convert both dates to a plain number in YYYYMMDD form, subtract, and take the whole-year quotient:

Age =
VAR Birthdate = Customer[Birth Date]
VAR ThisDay = TODAY ()
VAR IntBirthdate = YEAR ( Birthdate ) * 10000 + MONTH ( Birthdate ) * 100 + DAY ( Birthdate )
VAR IntThisDay = YEAR ( ThisDay ) * 10000 + MONTH ( ThisDay ) * 100 + DAY ( ThisDay )
VAR Age = QUOTIENT ( IntThisDay - IntBirthdate, 10000 )
RETURN
    IF ( NOT ISBLANK ( Birthdate ), Age )

It reads as more work than it is. Encoding a date as 20260412 makes the comparison purely positional: the year digits dominate, and the month and day digits act as a tie-breaker that only matters when the years are equal. Dividing the difference by 10,000 and discarding the remainder gives you completed years and nothing else.

That last IF is not decoration. A blank birth date would otherwise be treated as year zero and return an age in the two thousands, which is the kind of value that sails through a visual and gets caught in a board meeting.

Wrong numbers are expensive precisely because they look right. The same is true of traffic you cannot explain. Get a free SEO audit and find out what your site is actually ranking for.

Why DATEDIFF with YEAR is the wrong tool

Microsoft documents DATEDIFF with a specific and easily misread description. The syntax is:

DATEDIFF(<Date1>, <Date2>, <Interval>)

and the documented return value is "the number of interval boundaries between two dates." The allowed intervals are SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER and YEAR. The result is positive when Date2 is later than Date1 and negative when the arguments are the other way round.

Boundaries counted is not the same as years completed

The phrase worth sitting with is "interval boundaries." A boundary is a line you cross, not a span you finish. Age, in every ordinary and legal sense, is a span you finish: you are 39 until the moment your fortieth birthday arrives, no matter how close it is.

Those two definitions agree for most of the year and disagree near birthdays, which is exactly where age bands are drawn. That is the worst possible place for a metric to be ambiguous.

Why this slips through testing

The failure is invisible in the way people usually check a measure. Pick a birth date in the middle of the year, compare the result against a mental calculation, and it matches. The discrepancy only appears for rows whose birthday has not yet occurred in the current year, which is roughly half your table on any given day but never the half you happen to spot-check.

If you want to satisfy yourself one way or the other, do not reason about it. Build a two-row test table with a birth date in early January and one in late December, both from the same year, and look at what each formula returns today.

Dividing by 365 is worse, not simpler

The other popular shortcut is DATEDIFF in days divided by 365, or by 365.25 for the conscientious. Both are approximations dressed as arithmetic. The 365 version drifts upward over a lifetime because it ignores leap days; the 365.25 version drifts in the other direction for anyone whose life has contained an unrepresentative number of them. Neither produces a number that reliably flips on the right day.

What about YEARFRAC?

For years the recommended fix was to wrap YEARFRAC in INT:

Age = INT ( YEARFRAC ( Customer[Birth Date], TODAY (), 1 ) )

The third argument matters. A basis of 1 tells the function to use actual day counts rather than a simplified 30-day-month convention, and without it the result is not birthday-accurate at all.

This is better than DATEDIFF, and for a long time it was the standard advice. It has since fallen out of favour for two reasons, both of which the DAX community has documented in detail.

The first is precision. YEARFRAC returns a decimal, and that decimal can land fractionally below the whole number it should be. The documented example runs from 16 October 2000 to 16 October 2019 — exactly nineteen years, on the birthday itself — and returns slightly under 19, so INT truncates it to 18. A formula that is wrong on the birthday is wrong on the one day people check.

The second is performance. YEARFRAC is not a fast function, and age is typically calculated across every row of a customer dimension. On a large table the integer-arithmetic version is materially cheaper for a result that is also more correct.

Precision problems compound quietly in analytics and in search alike. Request a free audit and get a clear read on where your organic traffic is leaking.

How to build the age calculation, step by step

1. Decide what "today" means

Before writing anything, settle whether age should move with the calendar or be fixed to a reporting date. These are different requirements and the formula differs by one line.

• Age as of now: use TODAY ().
• Age as of a fixed reporting date: use DATE ( 2026, 1, 1 ) or whatever the agreed date is.
• Age at the time of an event, such as age at purchase: use the date column from the fact table rather than TODAY ().

The third case is the one most often got wrong. "Average age of customers" and "average age of customers at the moment they bought" are different questions, and only one of them is stable over time.

2. Check the data type of the birth date column

YEAR, MONTH and DAY need a genuine date or datetime column. If the birth date arrived as text — which is common from CSV exports and from systems that store dates in a local format — the formula will either error or silently coerce in a way that depends on locale.

Fix this in Power Query rather than in DAX. Set the column type to Date at the source step, and if the source format is ambiguous, use the locale-specific type conversion so that 03/04/1990 is parsed the way the source system meant it.

3. Write it as a calculated column

Age =
VAR Birthdate = Customer[Birth Date]
VAR IntBirthdate = YEAR ( Birthdate ) * 10000 + MONTH ( Birthdate ) * 100 + DAY ( Birthdate )
VAR IntToday = YEAR ( TODAY () ) * 10000 + MONTH ( TODAY () ) * 100 + DAY ( TODAY () )
RETURN
    IF (
        NOT ISBLANK ( Birthdate ),
        QUOTIENT ( IntToday - IntBirthdate, 10000 )
    )

A calculated column gives you a value you can slice by, which is almost always what age is for. Putting it on the customer table lets you build age bands, use it as a legend, and filter with it.

4. Add the banding you actually report on

Raw age is rarely the deliverable. Bands are. Write them explicitly rather than relying on a visual's automatic grouping, so that the boundaries are documented in the model:

Age Band =
VAR a = Customer[Age]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( a ), "Unknown",
        a < 18, "Under 18",
        a < 25, "18-24",
        a < 35, "25-34",
        a < 45, "35-44",
        a < 55, "45-54",
        a < 65, "55-64",
        "65+"
    )

Using < consistently with the lower bound of the next band removes the off-by-one arguments that otherwise surface every time someone audits the report.

5. Add the measure for aggregates

If you also need an average, write it as a measure over the column rather than recalculating age inside the aggregation:

Average Age = AVERAGE ( Customer[Age] )

When to use a calculated column and when to use a measure

The choice is not stylistic. It changes when the number is computed and therefore when it goes stale.

• A calculated column is evaluated at data refresh and stored. It is sliceable, fast at query time, and frozen until the next refresh.
• A measure is evaluated at query time. It cannot be used as a slicer axis in the normal way, but it reflects filter context.

For age specifically, the column is usually right, with one important consequence covered below.

Limitations and common mistakes

• TODAY () in a calculated column does not update by itself. It is evaluated at refresh. A model that refreshes weekly will report a customer's age up to seven days late, and a model that has stopped refreshing will keep reporting last month's ages indefinitely with no visible error.
• Blank birth dates produce absurd ages. Guard with ISBLANK every time. The alternative is DIVIDE ( Age, NOT ISBLANK ( Birthdate ) ), which returns a blank instead of a number, if you prefer that to an IF.
• Future birth dates produce negative ages. Data entry errors and placeholder dates such as 9999-12-31 both do this. Decide whether to show them, blank them, or flag them, but decide deliberately.
• Age bands drawn with <= on the upper bound overlap the next band. Pick one convention and apply it throughout.
• Time components break equality comparisons. If the birth date column is a datetime with a stray time portion, the day-level arithmetic still works, but any direct comparison against a date will not. Strip the time at the source.
• Age at event is not age today. Re-read the requirement before shipping. This single misreading accounts for more incorrect age reporting than any formula bug.

Assumptions nobody wrote down are where reporting goes wrong. Book a free audit and see which assumptions about your search traffic do not survive contact with the data.

Age in DAX versus the alternatives

Calculate it in the source system

If the birth date lives in a database you control, a computed column in SQL is a defensible place for age, and it keeps one definition for every tool that reads the table. The drawback is the same staleness problem in a different location, unless the column is a view that evaluates on read.

Calculate it in Power Query

Power Query can do this too, and for a one-off import it is fine. The reason to prefer DAX is that Power Query's result is baked in at load, and the expression language is less familiar to the next person who opens the file.

Let the visual group it

Power BI's automatic binning will happily turn an age column into buckets without any DAX. It is quick, and it is appropriate for exploration. It is not appropriate for a report somebody acts on, because the boundaries are not visible in the model and nobody can audit them later.

Use a date dimension

The most robust option for heavy age analysis is a proper date table related to the birth date, with age brackets precomputed as columns. This moves the logic out of a formula and into data, which is easier to test and easier to explain.

How do you verify the calculation is right?

Do not trust a glance at the report. Build a deliberate test:

• One row with a birthday that has already passed this year.
• One row with a birthday still to come this year, from the same birth year as the row above. These two should differ by exactly one.
• One row whose birthday is today. This is the row that catches precision bugs.
• One row with a blank birth date.
• One row with a birth date in the future.

If all five behave as you expect, the formula is sound. If the two same-birth-year rows return the same age, you are counting boundaries rather than completed years and you have the original problem back.

Final Thoughts

Age is a good illustration of a general pattern in DAX: the function whose name matches the question is often not the function that answers it. DATEDIFF sounds like it was built for this and is documented as counting boundaries, not completed spans. The slightly awkward integer-arithmetic pattern is both faster and more defensible, and it fails loudly rather than quietly.

The broader habit worth taking from this is to test formulas at their edges rather than in their middles. Mid-year birth dates will agree with almost any approach you try. The rows that disagree are the ones near a boundary, and those are precisely the rows your age bands are built on.

If you are writing more of this kind of logic, our guide to how to write DAX functions in Power BI covers measures, calculated columns and filter context in more depth, which is the ground this calculation sits on.

Clean data deserves traffic that matches it. Get a free SEO audit and find the organic opportunities your competitors have not taken yet.