Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. But, since DAX is the preferred languagein several analysisin Power BI, many are not aware of the feature available in Power Query. In this blog I'm going to describe how simple to calculateAge in Power BI through Power BI. The methodis exceptionally efficient when the Age calculationcan be performed on an already calculated row-by-row basis.

Calculate Age from a date

This is the DimCustomer table that is part of the AdventureWorksDW table that has an age column. I've removed several of the columns that aren't needed for easier reading;

If you want to calculate the age of each customer the only thing you need do is:

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

That's all there is to it. This will calculate an amount that is equal to the sum of the column for Birthdate column, together with the current date and time.

However, the age displayed under the Age column doesn't seem to be an actual age. It's because it's not a period.

Duration

Duration is an exclusive data type that is found inside Power Query which represents the distinctions between two DateTime values. Duration is made up of four values:

days.hours.minutes.seconds

This is how you interpret the data above. But from a user's point of view, it's not a good idea to find specifics such as that. There are ways you can locate each of the parts of the duration. By selecting the Duration menu it will show you can get the number of seconds or minutes, hours days, and years from it.

To apply the method of calculating the age in years such as you can choose Total Years.

It is important to note that the length of this program measured in days . Then, it is divided by 365, which will provide you with the annual value.

Rounding

To conclude, nobody claims that their Age is 53.813698630136983! They use the term 53, and then round it down. You can choose Rounding and Round Down in the Transform tab.

This will give you the number in years:

You can then tidy up the other columns, should you like (or maybe you've applied transformations with the Transform tab to prevent the creating columns) and name this column: Age:

Things to Know

• Refresh: The age calculated using this method is refreshed at the time when your database is refreshing. Every time, the system will compare your date of birth with the date and day on which the data refresh was made. This method will provide an older calculation for age. If you need calculations of the age to be carried out quickly using DAX Here's how I described how to apply.
• How to utilize Power Query The advantages of performing age calculations in Power Query is the fact that the calculation takes place during the refresh of your report. It is accomplished by making use of an instrument which makes the calculation simple, and it won't be any additional overhead when calculating it using DAX as a measure of runtime.
• Other scenarios It is not for calculating age only by birthdate. This is a great way to determine product inventory and for the different between and two date ranges one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc in Computer engineering. The engineer has over 20 years' expertise in data analysis databases programming, BI, and development predominantly with Microsoft technologies. He is an official Microsoft Data Platform MVP for nine consecutive years (from 2011 till now) because of his dedication to Microsoft BI. Reza is known as a prolific author and co-founder at RADACAD. Reza is also co-founder and coordinator of 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 has written several ebooks about MS SQL BI and also is working on a few more. He was also a regular member of online forums on technical issues like MSDN and Experts-Exchange . He also served as the moderator for MSDN SQL Server Forums, which is where he holds the MCP, the MCSE. He is also an MCITP for BI. He is the head of the New Zealand Business Intelligence users group. In addition, he's also the co-author of the highly praised Power BI from Rookie to Rock Star, which is free and has more than 700 pages of content and 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 desire is to help users find the ideal data solution. He is a Data enthusiast.This post was published as part of 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 guide to bookmark.

Post navigation

Share Different Visual Pages with different Security Groups Power BI's BIAge Calculation that is able to calculate Leap Year within Power BI using Power Query (see below).