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 computationsin Power BI, many don't know about this feature that is available in Power Query. In this article, I'm going to explain how easy it is to calculateAge in Power BI by using PowerBI. It's a methodis extremely efficient when your estimate of your agecan be calculated using a pre-calculated row the row basis.

Calculate Age from a date

Below you can view the DimCustomer table, which makes up the AdventureWorksDW table, which includes a birthdate column. I've removed some of the columns that aren't needed to make it easier to be read

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

• In Power BI Desktop, Click on Transform Data
• In Power Query Editor window; start by choosing the column for Birthdate.
• Go to the Add Column Tab, and then click on the "From Date & Time" section. And under Date Select the appropriate age range.

That's all there is. This is how you calculate the amount which is the total of the column for birthdate, Birthdate column, and the time and date column.

However, the age that appears in the Age column, it doesn't actually appear to be an age. It's because it's an actual duration.

Duration

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

days.hours.minutes.seconds

And that's how you interpret the numbers above. However, from the perspective of the user, it's not the norm for them to know the specifics of that. There are methods that are able to obtain every portion of the duration. When you select the Duration menu there is a way to get the duration in seconds as well as minutes, hours days , and years from it.

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

The duration is calculated in days and was then divided in 365days to provide the annual amount.

Rounding

The bottom line is that no one declares your age in 53.813698630136983! They are saying 53, which is reduced to a lower number. You can select Rounding and Round Down from the Transform tab for it.

This will let you know what your age is in terms of years

You can clean other columns, should you like (or there could be that you utilized transforms from the Transform tab to prevent making new columns) You can name this column as Age:

Things to Know

• Refresh The data's age that is calculated this way will be refreshed during the time of refreshing your database. Every time the system will be capable of comparing the date of birth to the date and time during the process of refreshing. The method involves an algorithm for pre-calculating the age. If you would like your age calculation to be done in a dynamic manner using DAX Here is the procedure I have described the method I would recommend making use of.
• The motive behind Power Query: Benefits from using age calculation in Power Query is that the calculation is performed when you refresh your report. It is accomplished by using an instrument that makes calculation easy and quicker, and there's not any additional expense in the calculation using DAX as a measure of runtime.
• Other scenarios These are not meant for the calculation of the date of birth. 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 from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc with a major with a concentration in Computer engineering. Over 20 years' experience working in the field of data analysis database, BI, development, and programming generally with Microsoft technologies. He has been an Microsoft Data Platform MVP for nine consecutive years (from 2011, to now) because of his love of Microsoft BI. Reza is a prolific writer and co-founder at RADACAD. Reza is also co-organizer and co-founder 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 also wrote a few books about MS SQL BI and also is working on additional books. He was also an active participant in online forums for technical issues such as MSDN and Experts-Exchange and was moderator on the MSDN SQL Server forums and holds the MCP and an MCSE as well being an MCITP in BI. He is also the head of the New Zealand Business Intelligence users group. They are also the authors of the book that is popularly referred to as Power BI from Rookie to Rock Star, which is absolutely free and contains nearly 1700 pages of content as well as a companion book called Power BI Pro Architecture published by Apress.
It 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 people find the best options for data. he's a Data enthusiast.This post was filed into Power BI, Power BI from Rookie to Rockstar, Power Query and is listed under Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great resource to save to your bookmarks.

Post navigation

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