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.
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.
- 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.