Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, because DAX is the most popular language usedin several calculationsin the Power BI platform, a lot of people are unaware of this function in Power Query. In this post, I will describe how simple to calculateAge in Power BI using PowerBI. The methodis very beneficial when the computation of an agecan be performed on a previous calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that is part of the AdventureWorksDW table which as a birthdate column. I've taken out some columns that aren't necessary for clarity;

To calculate what is the average age each buyer, you must have:

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

That's all there is to it. This will calculate an amount that is the total of the Birthdate column as well as the current date and the time.

But, the age that appears in the Age column, does not necessarily appear as an age. It is due to an actual duration.

Duration

Duration is a particular type of data used for Power Query which represents the variation between the two DateTime values. Duration is the result of four numbers:

days.hours.minutes.seconds

This is how you look at the above data. For one's own perspective, you don't want them to seek information like this. There are ways to get each portion that is what you need to know about time. When you go to the Duration menu you'll discover that you can extract the amount of seconds or minutes in addition to days, hours, and years from it.

Help in calculating the age in years like, for instance it is easy to select Total Years.

Be aware that the duration of this program calculated in days . Then, it is divided into 365 in order to provide you with the value for the year.

Rounding

Finally, no one says they are 53.813698630136983! They refer to it as 53, then they round it down. It's easy to choose Rounding , and Round Down by clicking on the Transform tab.

This will show you your age in years:

It's also possible to clean other columns if you'd like (or perhaps you've made use of transformations within the Transform tab to avoid creating of additional columns) This column can be renamed to Age: column and Age:

Things to Know

• Refresh The age calculated using this method will be updated every time you are refreshing your database. and each time will compare the birthdate to the date and the date of the refresh. This method is an earlier calculation of the age. If you're looking for the calculation to be dynamically done using DAX, here I provided a method you can apply.
• The reason behind Power Query: Benefits of doing age calculations using Power Query is that the calculation takes place when you refresh your report. This is done by using a tool that makes the calculation easier, and there's no added cost of doing it with DAX to determine the runtime.
• Another option is to use these numbers in use to calculate age, only beginning from the birth date. This can be used to calculate the date of inventory for items, as well as the distinction of two date or times one another.

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 over 20 years' experience in the field of data analysis data as well as BI, databases development and programming primarily on Microsoft technologies. He was a Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) for his commitment to Microsoft BI. Reza has been an active writer and co-founder at RADACAD. Reza is also co-founder and coordinator of Difinity Conference which is held in 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 a couple of publications about MS SQL BI and also is working on more books. The author was a frequent member on online technical forums like MicrosoftDN as well as Experts-Exchange and was the moderator for MSDN SQL Server forums, and is an MCP in addition to MCSE, MCP, and MCITP for Business Intelligence. He is the creator of the New Zealand Business Intelligence users group. Additionally, he's the writer of the famous text Power BI from Rookie to Rock Star, which is free and has more than 170 pages of information and is a component of Power BI Pro Architecture published by Apress.
It is an International Speaker at 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 users find the most effective data solution. He is a Data enthusiast.This entry was posted within Power BI, Power BI from Rookie to Rockstar, Power Query and is filed within Power BI, Power BI from Rookie to Rock Star, Power Query. Bookmark the permalink.

Post navigation

Share various visual pages by different security groups in Power BIAge Calculation in Years which is able as a way to calculate Leap Year in Power BI using Power Query