Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, because DAX is the most popular languagein many testsin Power BI, many do not know about the option available within Power Query. In this blog I'm going to show you how simple to calculateAge in Power BI by using Power BI. The methodis extraordinarily effective when Age calculationcan be done by calculating the row-by-row basis.

Calculate Age from a date

That's the DimCustomer table from the AdventureWorksDW table which includes a birthdate column. I've removed a few the columns that aren't needed for easier reading;

If you'd like to calculate the age of each customer, all you have to do is to:

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

That's it. This can calculate an amount that is the sum of the Birthdate column, along with the current date and time.

But, the age you can see in the Age column, doesn't appear to be an age. This is because it's a duration.

Duration

Duration is a distinctive data type found on Power Query which represents the distinctions between the two DateTime values. Duration is a mixture of four values:

days.hours.minutes.seconds

And that's how you take the information above into consideration. But from the user's perspective, you don't want them to find specifics such as that. There are ways you can fetch each part from the length. Selecting the Duration menu it will show you can extract the number of seconds or minutes, hours days, years, and seconds from it.

For for the method of calculating the age in years such as you simply select Total Years.

Be aware that the duration for the course is measured by days. This is then divided by 365 days to give you the year-long value.

Rounding

To conclude, nobody says their date of birth is 53.813698630136983! They use the term 53 with a rounding down. Select Rounding and Round Down on the Transform tab for it.

This will give you the age in years:

Then you can clear the other columns, should you like (or maybe you've performed transformations with the Transform tab to avoid any formation of columns) And name this column as Age:

Things to Know

• Refresh The estimated age calculated by the method shall be updated during the process of refreshing your database. And each time it will compare your date of birth to the date and the date the data refresh was made. The method provides an earlier estimation of the age. If you require your calculation to take place in a dynamic manner using DAX Here's a detailed explanation of the method you can apply.
• How to utilize Power Query The advantages of doing age calculations using Power Query is that the calculation takes place when you refresh your report. This is done by making use of an instrument which makes the calculation simple, and there won't be additional work involved in doing it using DAX for determining of runtime.
• Additional scenarios It is not to calculate age solely by the date of birth. This is a great way to establish inventory levels for products and for the differences between the dates of two days or times each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering. The engineer has over 20 years of experience in data analysis , database, BI, programming and development mostly based on Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years in a row (from 2011 until today) because of his dedication to Microsoft BI. Reza is known as a prolific author and co-founder of RADACAD. Reza is also co-founder and director of Difinity conference 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 several book on MS SQL BI and also is writing several more. He was also a regular member of online forums on technical matters like MSDN and Experts-Exchange . He was the moderator for MSDN SQL Server forum, and is a MCP and MCSE certifications as well as an MCITP of BI. He is also the director of the New Zealand Business Intelligence users group. He is also the co-author of the book popularly praised Power BI from Rookie to Rock Star, which is free and contains hundreds of pages of material in addition to The Power BI Pro Architecture published by Apress.
Speakers are an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday as well as SQL Users Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help users find the ideal data solution. He is an avid Data enthusiast.This article was released 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. This is an excellent resource for you to save.

Post navigation

Share different visual pages by utilizing different security groups in Power BI's BIAge Calculation which is used to calculate the leap year in Power BI using Power Query to calculate Leap Year.