Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, as DAX is the primary language usedin many calculationsin Power BI, many do not know this function offered by Power Query. In this blog , I'll explain how easy it is to calculateAge in Power BI by using PowerBI. It's a methodis extremely efficient in cases where the estimation of the agecan be calculated on a pre-calculated row or basis.

Calculate Age from a date

Below you can view the DimCustomer table, which can be found in the AdventureWorksDW table. It also has a birthdate column. I've removed several of the columns that aren't needed in order to make it simpler to understand;

To calculate the actual age of each buyer all you need to do is:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; start by selecting the column with the birthdate.
• Go to the Add Column Tab, and then select"Add Column". Then, click the "From Date & Time" section. And under Date Select the appropriate age range.

That's all there is. This will calculate an amount that is the total of the column for birthdate, Birthdate column, and the date and time.

The age, however, as it appears in the Age column, it doesn't actually appear to be an age. This is because it's an actual Duration.

Duration

Duration is a distinctive type of data structure within Power Query which represents the differentiating factors from the two DateTime values. Duration is a combination of four different values:

days.hours.minutes.seconds

And that's how you look at the numbers above. But from the standpoint of the user, it's not expected of them to be able to interpret such information. There are methods that can obtain each section of the duration. With the Duration menu you'll be able to determine the number of seconds, minutes, hours, the days and years out of it.

To aid in calculating the age in years by way of example it is easy to click on Total Years:

The duration is calculated in days and was then divided in 365 to yield the value of the year.

Rounding

It's the truth, nobody declares you old in 53.813698630136983! They say 53, which is reduced to a lower number. You can select Rounding and Round Down on the Transform tab.

This will tell you the age you've reached in your years.

It is then possible to clean other columns if you'd like (or it could be that you utilized transforms from the Transform tab to avoid creating new columns.) It is possible to name this column: Age

Things to Know

• Refresh The data's age determined using this method will be refreshed during the time of refreshing your database. Every time, the system will be capable of comparing the date of birth with the date as well as the duration of refreshing. This method uses an algorithm to calculate the age. If you'd like the age calculation to be performed dynamically with DAX this is the method I explained how to make use of.
• The reason behind Power Query: Benefits of using age calculation using Power Query is that the calculation is done at the time of refreshing your report. This is achieved by using the power of a tool that makes calculation much easier and faster, and there's not any additional expense to calculate it using DAX in order to determine runtime.
• Other scenarios These are not designed to calculate age from birth date. It can be used to calculate the time of inventory on products and also the differing dates and dates from one other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc with a concentration with a concentration in Computer engineering. Over 20 years' experience in the field of data analysis database, BI, programming, and developing mostly with Microsoft technologies. He has been an Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) for his passion for Microsoft BI. Reza is known as a prolific writer and co-founder at RADACAD. Reza is also the co-organizer and co-founder of the Difinity event 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 wrote some books on MS SQL BI and also is writing various other books. He was also a regular member of technical forums online like MSDN as well as Experts-Exchange and was moderator for MSDN SQL Server Forums as well as an MCP, MCSE and with an MCITP in BI. He also serves as the leader of the New Zealand Business Intelligence users group. They are also the authors of the book extremely well-loved Power BI from Rookie to Rock Star, which is totally free and comes with more than 1700 pages and an additional book called Power BI Pro Architecture published by Apress.
It is an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL group for users. And He is a Microsoft Certified Trainer.
Reza's aim is to assist people find the best solutions to manage data. Reza is an avid Data enthusiast.This post was filed within Power BI, Power BI from Rookie to Rockstar, Power Query and is listed under Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic guide to bookmark.

Post navigation

Share Different Visual Pages with different Security Groups Power-BIAge Years Calculation works for Leap Year in Power BI using Power Query