Age Calculation in Power BI using Power Query

Power Query has a simple way of calculating the age. However, because DAX is the popular language in many calculations in Power BI, many are not aware of this function in Power Query. In this blog article, I am going to show you how easy is to calculate Age in Power BI using Power BI. This method is very useful when the age calculation can be done as a pre-calculated row by row basis.

Calculate Age from a date

Below is the DimCustomer table from AdventureWorksDW table which as a birthdate column. I have removes some of the extra columns to make it easier to read;

To calculate the age of each customer, all  you need is to:

• In Power BI Desktop, Click on Transform Data
• In Power Query Editor window; select the Birthdate column first
• go to Add Column Tab, under “From Date & Time” section, and under Date, select Age

That’s it. this will calculate the difference between the Birthdate column, and the current date and time.

However, the age that you see in the Age column, doesn’t really look like an age. That is because it is a Duration.

Duration

Duration is a specific data type in Power Query which represents the difference between two DateTime values. duration is a combination of four values:

days.hours.minutes.seconds

and that is how you can read the values above. However, for the user’s point of view, you don’t expect them to go and read the details like that. there are ways that you can fetch each part of the duration. using the Duration menu option, you will see that you can extract the number of seconds, minutes, hours, days, and years from it.

For calculating the age in years, for example, you can simply click on Total Years:

Note that the duration is calculated in days and then divided by 365 to give you the yearly value.

Rounding

In the end, no one says their age as 53.813698630136983! they say it as 53, rounded down. You can easily select the Rounding and round down from the Transform tab for it.

This will give you the age in years:

You can then clean the other columns if you want (or you could have used transformations from the Transform tab to avoid the creation of new columns), and call this column; Age:

Things to Know

• Refresh: The age calculated this way will get updated at the time of refreshing your dataset. and each time will compare the birthdate with the date and time of the refresh. This method is a pre-calculation of an age. If you, however, need the age calculation to be done dynamically using DAX, here I explained a way that you can use.
• Why Power Query: Benefits of doing age calculation in Power Query is that the calculation is done at the time of refreshing your report, using a tool that makes the calculation simpler, and there won’t be extra overhead of calculating it using DAX as a measure runtime.
• Other scenarios: this is not for calculating age only from the birthdate. this can be used for product inventory age and the difference between two dates and times from each other.

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.