Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, because DAX is the most popular language usedin many calculationsin Power BI, many do not understand this capability that is available in Power Query. In this article, I'll go over how simple it is to calculateAge in Power BI with PowerBI. This methodis very efficient when the estimation of your agecan be based on a pre-calculated row or the row basis.

Calculate Age from a date

Below, you'll find the DimCustomer table which makes up the AdventureWorksDW table. The table has the birthdate column. I've removed a few of the columns that aren't required to make it easier to look at;

To calculate the actual average age of every buyer, you'll need:

• In Power BI Desktop, Click on Transform Data
• In Power Query Editor window; start by choosing the column with the birthdate.
• go to the Add Column Tab, then select"Add Column", then select the "From Date & Time" section. And under Date you can select the age range.

That's it. It will calculate an amount that is the total of the Birthdate column, as well as the date and time.

The age, however, as it appears as a number in the Age column, it doesn't actually seem to be an actual age. This is due to the fact that it's an actual duration.

Duration

Duration is a unique kind of data format within Power Query which represents the differences in two DateTime values. Duration is a mixture of four values:

days.hours.minutes.seconds

and that's how you can read the numbers above. But from the viewpoint of the user, it's not expected of them to be able to interpret such information. There are ways you can obtain each section of the duration. Utilizing the Duration menu option, there is a way to determine the number of seconds or minutes, hours, days , and years from it.

To help in calculating the age in years as an example, it is simple to select Total Years:

The duration was calculated in days . It was then divided in 365days to yield the value of the year.

Rounding

At the final point, nobody declares your age as 53.813698630136983! They are saying 53, which is round down. Select Rounding or Round Down from the Transform tab for it.

This will let you know that you're old enough to be

You can then clean the other columns, if you wish (or there could be that you used transformations within the Transform tab to avoid creating new columns.) The column can be named column"Age:

Things to Know

• Refresh The data's age that is calculated in this manner will be refreshed each time you refresh your database. Each time it is refreshed, the system will be competent to match the birthdate to the date and time during the process of refreshing. In this method is an algorithm to predict the age. If you wish the calculation of age to be done in a dynamic manner using DAX here is how I explained how to make use of.
• The motive behind Power Query: Benefits of using age calculation using Power Query is that the calculation is performed at the time of refreshing your report. This is made possible by the power of a tool that makes calculation significantly faster and easier, and there's no added cost to calculate it using DAX for a measurement of runtime.
• Other scenarios These are not intended to be used to calculate the age of a person based on their birth date. It can be used to calculate the age of inventory in the case of products and also to determine the differences in dates and dates that differ from one other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has an BSc with a major with a concentration in Computer engineering. There are more than 20 years work experience in the field of data analysis and BI, databases, programming, and developing mostly with Microsoft technologies. He has been an Microsoft Data Platform MVP for nine years in a row (from 2011, to now) in recognition of his love for Microsoft BI. Reza is known as a prolific writer and co-founder of RADACAD. Reza is also co-founder and organizer of the Difinity event 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 books on MS SQL BI and also is writing some other books. He was also a regular forum member on online technical forums like MSDN as well as Experts-Exchange and was moderator in the MSDN SQL Server forums as well as an MCP and MCSE , as well as an MCITP for BI. He also is the leader of the New Zealand Business Intelligence users group. This group is also the writer of the book that is loved by many. Power BI from Rookie to Rock Star, which is entirely free and comprises nearly 1700 pages of material as well as a companion book called Power BI Pro Architecture published by Apress.
They are an International Speaker in 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 people find the best options for data. is a Data enthusiast.This blog post was posted in 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 is a fantastic guide to bookmark.

Post navigation

Share different Visual Pages using different Security Groups Power BIAge Years Calculation works for Leap Year in Power BI using Power Query