Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way of calculating the age. However, since DAX is the primary language usedin numerous analysesin Power BI, many don't know about this option within Power Query. In this article I will show how easy it is to calculateAge in Power BI in conjunction with PowerBI. The methodis extraordinarily helpful in situations where you need in order to calculate the estimation of the agecan be done on an earlier calculated row by row basis.

Calculate Age from a date

Here is the DimCustomer table, which is part of the AdventureWorksDW table and is the column with the birth date. I've removed the additional columns so that it is easier to read.

If you're trying to calculate an age range for every client all you need is to:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window select the Birthdate column first
• Click on 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 to it. This is how you calculate any changes between the Birthdate column, as well as the current date and hour.

However, the age appears under"the Age column, and doesn't seem to be an actual number. That is because it is an actual Duration.

Duration

Duration is a unique kind of Data Type found in Power Query which represents the different between two DateTime values. Duration is a mixture from four different numbers.

days.hours.minutes.seconds

That's how you'll get the information above. However, from the user's perspective, don't expect them to read details like those above. There are different ways to make each section of time. If you click on the Duration menu You will find that you are able to take the duration of seconds and minutes as well as hours, days and years from it.

To aid to assist calculating the age in years for instance just hit the number Total Year:

Take note that the duration of the time is calculated using days. It is after that multiplied by days, so that you get the annual value.

Rounding

At the final point, no one declares their age to be 53.813698630136983! They refer to it as 53, then round down. It is simple to choose Rounding and round down from the Transform tab.

This will give you an age range in years:

Then , you can clean the other columns that you'd like to (or it could be that you've utilized transformations on the Transform tab to keep from creating new columns.) You can also name this column: Age.

Things to Know

• Refresh: The age calculated by this method will be updated as of the date of refreshing your dataset. And each time, it will compare the date of birth with the time and date that the data refresh took place. This method is an earlier calculation of the age. If, however, you want the calculation to be made dynamically using DAX here's how I explained the method to use.
• What's the cause? Power Query Advantages of the age calculation feature in Power Query can be that the calculation takes place each time you refresh your report. This is accomplished using an algorithm that makes the calculation simpler and quicker, and there is no additional overhead to calculate it with DAX to measure runtime.
• Alternative scenarios used to calculate age only from the birthdate. It can be used to determine the date of inventory on products and also to determine the difference in two days or time periods from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He is a graduate of an BSc at the University of Computer engineering. He has more than twenty years' experience in data analysis database programming, BI, and development mostly with Microsoft technologies. He is an official Microsoft Data Platform MVP for nine consecutive years (from 2011 to today) because of his dedication toward Microsoft BI. Reza is an avid journalist and co-founder for RADACAD. Reza is also co-founder and director of Difinity conference in New Zealand. Difinity Conference 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 wrote several books on MS SQL BI and also is writing a few more. He also was a frequent participant in online forums for technical questions like MSDN and Experts-Exchange as well as the moderator for MSDN SQL Server forums, and holds the MCP, MCSE, and the MCITP for Business Intelligence. He is also the coordinator of the New Zealand Business Intelligence users group. Additionally, he is the creator of the highly acclaimed publication Power BI from Rookie to Rock Star, which is free and includes nearly 700 pages worth of information and The Power BI Pro Architecture published by Apress.
He is an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL users groups. And He is a Microsoft Certified Trainer.
Reza's love is helping users find the right information solution. He is a Data enthusiast.This post was published with 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 article is a good source to bookmark.

Post navigation

Use Different Visual Pages to Share by using Different Security Groups in Power BI's Year Calculation. This is used for Leap Year in Power BI through Power Query