Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. However, as DAX is the most popular languagein numerous analysesin Power BI, many don't have any idea about the feature available in Power Query. In this blog I'm going to show you how simple it is to calculateAge in Power BI through Power BI. The methodis very efficient when age calculationcan be carried out using a row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table of the AdventureWorksDW table which as a birthdate column. I've removed some of the columns that aren't needed in order to make it easier to read;

If you're looking to calculate the average age of your customers the only thing you need to do is to:

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

This is it. This can calculate an amount which is the sum of the Birthdate column, as well as the current date and the time.

However, the number you can see in the Age column doesn't seem to be an age. It's because it's a duration.

Duration

Duration is a special kind of data by using Power Query which represents the variations between two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

and that's how you can interpret the data above. But from users' perspective it's not a good idea to look up information like this. There are ways you can get each piece of information of the duration. When you select the Duration menu, it will reveal that you can determine the number of seconds minutes, hours days, and years from it.

For an method for calculating the age in years such as you simply go to Total Years.

Note that the length of your program will be measured in days . Then, it is divided by 365 to yield the annual amount.

Rounding

At the final point, nobody claims that their years of age are 53.813698630136983! They say 53, and rounding it down. It is possible to select Rounding and Round Down in the Transform tab.

This will show you your age in years:

After that, you can tidy up the other columns if you'd like (or perhaps you've applied transformations via the Transform tab to prevent the column creation) And name this column; Age:

Things to Know

• Refresh The estimated age calculated using this method can be updated when the time comes that your data is refreshed. And each time it will compare the date of birth to the date and date that the data refresh took place. In this method provides an earlier estimation of the age. If you need the calculation of age to be performed quickly using DAX Here's how I described how to use it.
• How to utilize Power Query The advantage that come from performing age calculations using Power Query is that the calculation is made as you refresh your report. This is done making use of an instrument that makes the calculation more straightforward, and there won't be additional cost in doing it using DAX because it is a measurement of runtime.
• Additional scenarios to calculate age solely by the date of birth. This is a good way to determine product inventory and to determine the difference between two dates or dates from each other.

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. There are more than 20 years' expertise in data analysis databases programming, BI, and development predominantly focused on Microsoft technologies. He has been a certified Microsoft Data Platform MVP for nine consecutive years (from 2011 to the present) due to his commitment to Microsoft BI. Reza has been a prolific author and co-founder of RADACAD. Reza is also co-founder as well as coordinator 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 books concerning MS SQL BI and also is writing a few additional. He was also an active member of online forums on technical questions like MSDN and Experts-Exchange and was the moderator for MSDN SQL Server Forums, as well as holding the MCP and the MCSE. He also holds an MCITP of BI. He is also the leader of the New Zealand Business Intelligence users group. Additionally, he's the creator of the book that is popularly praised Power BI from Rookie to Rock Star, which is free and includes more than 700 pages of information and The Power BI Pro Architecture published by Apress.
This speaker has been an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday in addition to SQL Users Groups. And He is a Microsoft Certified Trainer.
Reza's aim is to assist users find the best data solution. He is a Data enthusiast.This piece was released with the title 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 a fantastic source to save.

Post navigation

Share different visual pages by utilizing different security groups in Power BIAge's Age Calculation which can be used to calculate Leap Years in Power BI by using Power Query to calculate Leap Year.