Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way to calculating the age. However, since DAX is the preferred languagein several researchesin Power BI, many don't know about the functions available in Power Query. In this post, I'm going to describe how simple it is to calculateAge in Power BI using Power BI. This methodis very effective in cases where the age calculationcan be carried out using a row-by-row basis.

Calculate Age from a date

The table is DimCustomer table that comes from the AdventureWorksDW table which includes an age column. I've removed several of the extra columns to make it more readable;

If you want to calculate the number of years of age for each customer, all you have to do is to:

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

That's all there is to it. This will calculate an amount which is the sum of the Birthdate column, as well as the current date and time.

However, the date you see under the Age column, doesn't appear to be an actual age. It's because it's a time.

Duration

Duration is an exclusive data type used on Power Query which represents the difference between two DateTime values. Duration is a combination of four values:

days.hours.minutes.seconds

That's the way you take the information above into consideration. But from users' perspective you don't want them to look up information like that. There are ways you can extract each element by examining the length. Selecting the Duration menu it will show that you can find the amount of seconds days, minutes days, years, and seconds from it.

To utilize an method to use the method of calculating the age in years such as, for instance you simply select Total Years.

Be aware that the duration of this program calculated in days . Then, it is divided by 365, which will give you the yearly value.

Rounding

The final thing to note is that nobody claims that their age is 53.813698630136983! They refer to it as 53, with the rounding down. You can select Rounding as well as Round Down by clicking on the Transform tab.

This will give you an estimate of your age in years:

Then you can tidy the other columns if you'd like (or perhaps you've applied transformations via the Transform tab, to prevent new columns) and name this column: Age:

Things to Know

• Refresh: The age calculated through the method gets refreshed at the time the database refresh occurs. And each time it will compare your date of birth with the date and the date the refresh of data took place. In this method provides an earlier estimation of the age. If you're in need of an age calculation be completed dynamically with DAX, here I explained how to use it.
• How to utilize Power Query The advantages of performing age calculations in Power Query will be the calculation is performed as you refresh your report. It is accomplished by using an instrument that makes the calculation more easy, and there will not be extra overhead in calculating it with DAX in order to determine runtime.
• Other scenarios It is not for calculating age only on the basis of the date of birth. This is a fantastic way to establish inventory levels for products and for the different 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 of Computer engineering. Over 20 years of' working experience in data analysis database programming, BI and development primarily based on Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years in a row (from 2011 until now) due to his commitment to Microsoft BI. Reza is known as a prolific author and co-founder at RADACAD. Reza is also co-founder and co-chairman 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 working on several more. He was also an active participant in online forums for technical issues such as MSDN Experts-Exchange as well as the moderator for MSDN SQL Server Forums and holds the MCP and the MCSE. He is also an MCITP of BI. He is also the leader of the New Zealand Business Intelligence users group. He is also the writer of the book that is loved by many. Power BI from Rookie to Rock Star, which is gratis and contains hundreds of pages worth of content in addition to 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 as well as SQL users groups. And He is a Microsoft Certified Trainer.
Reza's dream is to help users to find the most efficient data solution. He is a Data enthusiast.This post was published in 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 resource to save.

Post navigation

Share Different Visual Pages with Different Security Groups in Power BI's BIAge Calculation that is able to calculate the leap year in Power BI with Power Query (see below).