Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, as DAX is the most popular languagein several analysisin Power BI, many do not know about the function available in Power Query. In this blog I'm going to show you how simple it is to calculateAge in Power BI through Power BI. The methodis extremely effective for situations where the Age calculationcan be made on an already calculated row-by-row basis.

Calculate Age from a date

The table is DimCustomer table that is part of the AdventureWorksDW table which includes a birthdate column. I've removed a few of the columns that aren't needed to make it easier to read;

If you'd like to calculate the average age of your customers the only thing you need to do is to:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window, choose the Birthdate column first.
• Click on the Add Column Tab. Under "From Date & Time" section, under Date select the date range.

That's all there is to it. This is able to calculate an amount which is the sum of the column for Birthdate column, together with the current date and time.

However, the date you see under the Age column doesn't seem to be an actual age. It's because it's a duration.

Duration

Duration is an exclusive data type that is found within Power Query which represents the difference between the two DateTime values. Duration is a mix of four different values:

days.hours.minutes.seconds

and that's how you can look at the data in the above. But from a user's point of view, you don't want them seek out details like this. There are ways to extract each element in the length. If you choose the Duration menu it will tell you that you can extract the number of seconds minutes, hours as well as days and years from it.

To apply for the method that involves calculating the age in years like, for example you can hit the Total Year:

Take note that the duration that the programme runs is measured in terms of days. It is then divided by 365, which will yield the annual amount.

Rounding

In the end, there is no consensus that says that the age is 53.813698630136983! They use the term 53, with the rounding down. It is possible to select Rounding and Round Down from the Transform tab.

This will give you an estimate of your age in years:

After that, you can tidy up the other columns, if desired (or maybe you've made a transformation through the Transform tab to avoid the new columns) Then name the column Age:

Things to Know

• Refresh The age determined through the method gets refreshed during the process of refreshing your database. And each time it will compare your date of birth to the date and day on which the data refresh took place. The method will provide an older calculation for age. If you're looking for that calculation to be carried out automatically using DAX Here's how I described the methods you can use.
• How to use Power Query Benefits of performing age calculations in Power Query can be that this calculations are made when you refresh your report. This is done making use of an instrument that makes the calculation more easy, and there will not be extra overhead in calculating it with DAX to measure runtime.
• Additional scenarios It is not to calculate age solely on the basis of birth date. This is a great way to measure inventory for products as well as for the distinction between two dates or dates each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc of Computer engineering. Over 20 years of of expertise in data analysis databases, BI, programming and development primarily based using Microsoft technologies. He has been a certified Microsoft Data Platform MVP for nine years (from 2011 till now) for his passion for Microsoft BI. Reza is also a prolific author and co-founder at RADACAD. Reza is also co-founder and co-organizer of the Difinity conference at New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He has also written a few publications in the field of MS SQL BI and also is working on several additional. He was also an active forum participant on online forums for technical questions like MSDN and Experts-Exchange and was the moderator of MSDN SQL Server forum which is where he holds the MCP, MCSE as well as an MCITP for BI. He is the leader of the New Zealand Business Intelligence users group. He is also the writer of the highly praised Power BI from Rookie to Rock Star, which is free and includes more than 700 pages of content and The Power BI Pro Architecture published by Apress.
Speakers are an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday along with SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help users find the right data solution. He is an avid Data enthusiast.This article was published under Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic resource to bookmark.

Post navigation

- Share Different Visual Pages with Different Security Groups in Power to access BIAge's Age Calculation that is able to calculate Leap Years in Power BI by using Power Query to calculate Leap Year.