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 preferred language usedin numerous analysesin Power BI, many do not know about this feature within Power Query. In this blog article, I am going to demonstrate how simple it is to calculateAge in Power BI with Power BI. The methodis extremely helpful in situations where you need to calculate the estimation of the agecan be done as an earlier calculated row by row basis.

Calculate Age from a date

Here is the DimCustomer table that is part of the AdventureWorksDW table, which is the birthdate column. I've removed the extra columns to make it easier to read;

If you want to calculate an age range for each client, the only thing you need is to:

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

That's all there is to it. This is how you calculate any difference in the Birthdate column as well as the current date and time.

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

Duration

Duration is a special type of data of data type in Power Query which represents the difference from two DateTime values. Duration is a mix of four numbers:

days.hours.minutes.seconds

and that is how you read the information above. However, from the user's point of view you shouldn't expect them to read particulars like the ones above. There are ways you can get each portion of the time. If you click on the Duration menu You will notice that you can extract the amount of seconds and minutes, hours, days, and years from it.

To help in calculating the age in years for instance just hit Total Year:

Note that the duration is calculated in terms of days. It is after that divided by the number of days, to give you the yearly value.

Rounding

In the end, no one claims that their age is 53.813698630136983! They call it 53, then rounded down. It is simple to choose Rounding and round down from the Transform tab.

This will provide you with the age in years:

Then you can tidy the other columns you'd like to (or you could have used transformations in the Transform tab to prevent making new columns) You can name this column: Age.

Things to Know

• Refresh: The age calculated this way will be updated at the time of refreshing your dataset. And each time, it will compare the date of birth with the date and time of the refresh. This method is an earlier calculation of age. If, however, you require the calculation to be done dynamically using DAX here is how I described how to use.
• What is the reason? Power Query: Benefits of using the age calculation feature in Power Query is that the calculation is carried out when you refresh your report. It is done using an algorithm that makes the calculation much easier and faster, and there is no additional overhead to calculate it using DAX as a measure runtime.
• Alternative scenarios It is not for calculating age only from the birthdate. this can be used for the age of inventory for products and also the different between two dates or times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds earned a BSc from Computer engineering. He is more than twenty years' experience in data analysis database, BI, programming, and development mostly with Microsoft technologies. He is an official Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) because of his dedication to Microsoft BI. Reza is a prolific journalist and co-founder for RADACAD. Reza is also co-founder and co-organizer of the Difinity event and 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 was also a regular participant in online forums for technical issues like MSDN and Experts-Exchange as well as the moderator on MSDN SQL Server forums, and holds the MCP, MCSE, and the MCITP for BI. He is the director of the New Zealand Business Intelligence users group. He is also the author of the highly acclaimed book Power BI from Rookie to Rock Star, which is free and contains more than 700 pages of content, as well as The Power BI Pro Architecture published by Apress.
The speaker 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 best information solution. He is Data enthusiast.This post was filed under Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great resource for you to bookmark.

Post navigation

Share Different Visual Pages by using Different Security Groups in Power BIAge's Years Calculation that is used for Leap Year in Power BI by using Power Query