Age in Years Calculation that works for Leap Year in Power BI using Power Query

I wrote a blog article yesterday about the Age calculation in Power Query, and how easy it is to calculate things. I mentioned in the blog that it won’t be precisely correct for scenarios that include leap years. However, I got some concerns that people are not still using it because they think this is totally wrong. So I have written a Power Query function that calculates the Age in Years, and it works perfectly correct even if the period includes leap year. This blog is about that function and how you can use it.

If you just want to jump into the function and usage of that, go to the section Creating the Function in this blog

When the Total Years is not exactly right

In Power BI, using Power Query, you can calculate the age very simple using only a few steps from the graphical interface, I explained the process in details here. That age calculation works fine, but not precisely correct. Let’s see when?

In the table above, the column Birthdate has some random dates. The Age column is calculating the Total Years from the duration generated by age, using the method explained here. To give you a way to calculate things yourself, I also included the column Current Date, so that you can compare with the Birthdate and check the age yourself, as you see, the age is correct for most of the values:

Even if the birthdate is on the 29th of a year (which is a leap year), still the calculation works fine for me. However, the calculation works based on this current date. if the current date is different then the calculation might not work correctly too. You can see that some rows are not returning correct age results.

These three values should be returning the age value as 29, because the current date is still 18th of Jan 2020. Now why they are not correct? the reason is that the Total Years in Power Query is calculated as a division of 365 regardless of the year being leap year or normal.

This is why even after a few days after the current date, the age calculation is still wrong, it might be a year more.

Nothing is wrong with the result if you take that division of 365 into consideration of course. However, when we calculate age in years, we want this to properly count years even if we have leap year within the period.

Custom Function

The function that you will see in this blog is giving you the correct result, here is an example:

This is a custom function in Power Query. In this blog post, I won’t explain how the custom function should be created and what it is, if you are interested to learn more about that, read my article here.

Creating the Function

There are many ways to calculate the age in years correctly in Power Query, I used one of those methods, and created a custom function. Here is the entire code of the function:

let
    Source = (BirthDate as date) as number=>
let
    fromBiggerthanTo=if BirthDate>Date.From(DateTime.LocalNow()) then true else false,
    dayofYearFrom=Date.DayOfYear(BirthDate),
    yearFrom=Date.Year(BirthDate),
    dayofYearTo=Date.DayOfYear(DateTime.LocalNow()),
    yearTo=Date.Year(DateTime.LocalNow()),
    countYears=yearTo-yearFrom-1,
    age=if (dayofYearFrom<=dayofYearTo) then countYears+1 else countYears
in
    if (fromBiggerthanTo) then -1 else age
in
    Source

To use this function, create a new Blank Query in Power Query Editor,

Go to the View tab, and click on Advanced Editor, replace that text with the script mentioned above entirely.

Then click on Done. The name of the function is also important. change the query name (which now has fx icon beside it, meaning it is a function) to AgeInYears.

Using the Function

To use the function, you can simply go to the query that you want this calculation to be added to that, then go to the Add Column tab, and click on Invoke Custom Function;

Choose the Function Query, and make sure the BirthDate parameter is coming from the column with date values in it.

Note that if you cannot find the function query AgeInYears, then you probably either have not yet created the function or did not renamed it as AgeInYears as mentioned in the previous step.

As you can see in the above screenshot, this function works perfectly fine for all dates, even if we have leap years in the range. You can compare the result of that with the normal Age calculation in the below screenshot

If you are looking for a very precise age in years calculation, this function is easy to use. You create it once per file (there is also a way to create a connector, and then re-use it as many times as you want in multiple files), and then re-use it.

Important Considerations

• The age calculation in Power Query is not wrong, it is just doing what it says. The total years divide the duration in days by 365.
• The method explained here should work for all combinations, if you find anything that doesn’t work, let me know and I’ll fix the function.
• if the birthdate is in the future (after the current date), then the function returns -1.
• You might wonder; should I do this calculation of age in Power Query or in DAX? the answer is: It depends on the fact that do you want the calculation to be dynamic? if yes, then do it in a measure using DAX, if not, then use this method to pre-calculate it using Power Query.

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.