Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method in calculating the age. But, because DAX is the primary languagein several functionsin Power BI, many are unaware of this feature within Power Query. In this blog post I'm going to explain how easy it is to calculateAge in Power BI using PowerBI. The methodis extremely useful in situations where it is necessary to do the calculations of agecan be done as an earlier calculated row by row basis.

Calculate Age from a date

Below is the DimCustomer table from AdventureWorksDW table which as the birthdate column. I've removed the extra columns to make it easier to read.

To calculate an age range for every consumer, all you need is to:

• In Power BI Desktop, Click on Transform Data
• In Power Query Editor window; start by selecting the Birthdate column.
• go to Add Column Tab, then under "From Date & Time" section, and under Date, select the age range.

That's it. this is how you calculate any difference in the Birthdate column, as well as the current date and time.

However, the age that appears when you look at the Age column, doesn't really look like an age. It's because it's a Duration.

Duration

Duration is a specific data type within Power Query which represents the differences of two DateTime values. Duration is a mixture of four values:

days.hours.minutes.seconds

This is how you read the numbers above. From an individual's perspective you shouldn't expect them to go and read the specifics like this. There are methods that can get each portion from the length. Utilizing the Duration menu You will notice that you are able to extract the amount of seconds or minutes, hours, days, and years from it.

To help in calculating the age in years by way of example it is easy to go to Total Years.

Take note that the duration of the program is calculated in days . It is was then divided in 365 to yield the yearly amount.

Rounding

It's the truth, no one says that their age is 53.813698630136983! they say it as 53, with a rounding down. You can easily select the Rounding option and then the round down from the Transform tab.

This will provide you with the age in years:

It is then possible to clean other columns, if you wish (or you could have utilized transformations using the Transform tab to avoid making new columns) You can name this column; Age:

Things to Know

• Refresh The date calculated in this manner will be updated every time you are refreshing your database. Each time, it will match dates of birth and the date and time that the data refresh took place. This method is a pre-calculation of an age. If you, however, require the calculation of age to be performed dynamically with DAX Here I have explained how you can employ.
• Reasons to choose Power Query: Benefits of performing age calculations in Power Query is that the calculation is done at the time of refreshing your report. It is done using an application that makes the calculation simpler, and there won't be extra overhead when calculating it with DAX as a measure of runtime.
• Additional scenarios : this is not used to calculate the age on the basis of birthdate. This could be used for inventory of products as well as the differences between two dates and dates from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds an BSc with a major in Computer engineering. He enjoys more than 20' experience in data analysis databases, BI, programmingand development primarily on Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years in a row (from 2011 till now) for his dedication in Microsoft BI. Reza is a prolific writer and is co-founder with RADACAD. Reza is also co-founder as well as co-organizer for the Difinity the conference held and 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 wrote several books on MS SQL BI and also is writing some others, He was also a regular member of online technical forums like MSDN and Experts-Exchange as well as moderator of MSDN SQL Server forums, and holds the MCP, MCSE, and MCITP of BI. He is the founder for the New Zealand Business Intelligence users group. Also, he's the author of the well-known book Power BI from Rookie to Rock Star, which is free and has more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
It is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL users groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help users find the best data solution. He's a Data enthusiast.This post was filed by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and related to Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

Share different visual pages using different security groups in Power BIAge's Age Calculation that can be used to calculate Leap Year in Power BI with Power Query