Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. However, because DAX is the preferred language usedin several functionsin Power BI, many do not know about this feature in Power Query. In this blog post I'm going to demonstrate how simple it is to calculateAge in Power BI using PowerBI. This methodis extremely beneficial for situations when an Age calculationcan be done as an already calculated row-by-row basis.

Calculate Age from a date

Below is the DimCustomer table that is part of the AdventureWorksDW table, which acts as a birthdate column. I've removed some the columns that aren't needed to make it easier to read.

In order to calculate the actual age of each buyer, you need is to:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; choose the first column, Birthdate.
• go to Add Column Tab, then under "From Date & Time" section, and then under Date, choose the age range.

That's it. this can calculate how much difference there is between Birthdate column and the current date and time.

The age you see on the Age column, doesn't look like an age. This is because it's a duration.

Duration

Duration is a unique form of data within Power Query which represents the differences between the two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

and that is how you read the above values. However, from the user's point of view they shouldn't be expected to go and read the information like that. There are ways you could get every part of the time. using the Duration menu option, you will see how you can extract the amount of seconds to minutes, hours days, and years from it.

To help in calculating the age in years by way of example it is easy to select Total Years.

Take note that the duration of the program is calculated in days and afterwards divided into 365, to give you the yearly value.

Rounding

At the final point, nobody says the age of their child as 53.813698630136983! they refer to it as 53, rounded down. It is simple to choose 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 desired (or the possibility is that you used transformations in the Transform tab to avoid the creation of new columns) This column can be renamed as Age: column; Age:

Things to Know

• Refresh The age calculated this way will get updated when refreshing your database. Every time, the system will be able to compare the birthdate to the date and timing for the refreshing. This method is a pre-calculation of an age. If you want the age calculation to be made dynamically using DAX This is the way I've explained an approach you can utilize.
• Why Power Query: Benefits of performing an age calculation using Power Query is that the calculation is performed at the time of refreshing your report. It is done using an application that makes the calculation simpler, and there's no need for the extra expense of doing it with DAX as a measure runtime.
• Alternative scenarios The Other scenarios are not meant to calculate age only from the birthdate. This is a good way to determine product inventory and for the differing dates and times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc in Computer engineering. He holds more than 20 years of experience in data analysis data, BI, databases developing, and programming mostly using Microsoft technologies. He is a Microsoft Data Platform MVP for nine consecutive years (from 2011 to the present) for his dedication in Microsoft BI. Reza is a prolific blogger and co-founder of RADACAD. Reza is also the co-founder and co-organizer of the Difinity the conference held 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 written several books on MS SQL BI and also is writing a few more. He was also a regular member of online technical forums like MSDN and Experts-Exchange and was also moderator of MSDN SQL Server forums, and holds the MCP, MCSE, and MSCITP of BI. He is the head for the New Zealand Business Intelligence users group. The group is also writer of the highly popular book Power BI from Rookie to Rock Star, which is completely free and includes more than 1800 pages of material and The Power BI Pro Architecture published by Apress.
A.J. is an International Presenter at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL group for users. And He is a Microsoft Certified Trainer.
Reza's dream is to help you find the best data solution. He's a Data enthusiast.This article was published on Power BI, Power BI from Rookie to Rockstar, Power Query and is filed under Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share Different Visual Pages using different security groups in Power BIAge's Year Calculation that can be used to calculate Leap Year in Power BI with Power Query