Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, because DAX is the most popular language usedin many calculationsin Power BI, many do not understand this capability of Power Query. In this blog post, I'll describe how easy it is to calculateAge in Power BI using PowerBI. It's a methodis extremely efficient for situations where your estimation of the agecan be performed on a pre-calculated row or basis.

Calculate Age from a date

Below is the DimCustomer table which forms part of the AdventureWorksDW table. The table has a birthdate column. I've removed some of the columns that aren't required to make it easier to look at;

In order to calculate the actual the age for each purchaser you'll need:

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

That's all there is. this calculates the calculate the amount which is the sum of the Birthdate column, and the date and time.

But, the age appears to be in the Age column, it doesn't actually appear to be an age. It's due to an actual duration.

Duration

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

days.hours.minutes.seconds

And that's how you read the numbers above. However, from the perspective of the user it is not required of them to be able to interpret such information. There are methods to ensure that you are able to obtain every part of the duration. By using the Duration menu, there is a way to remove the amount of seconds and minutes, hours, days and years from it.

To help in calculating the age in years through an example, it is easy to click on Total Years:

The duration was calculated in days and was then divided into 365, which will yield the value of the year.

Rounding

The bottom line is that nobody says your age is 53.813698630136983! The number is 53. This is round down. You can select Rounding and Round Down by clicking on the Transform tab.

This will reveal how old you are:

It is then possible to clean other columns, should you like (or there could be because you have used transformations within the Transform tab to avoid creating new columns) It is possible to name this column as Age:

Things to Know

• Refresh The information's age calculated this way will be refreshed when you refresh your database. Each time the system will be competent to match the birthdate to the date and time during the process of refreshing. That method uses an algorithm that is used to pre-calculate the age. If you would like the age calculation to be done dynamically using DAX here's how I have described the method I would recommend making use of.
• The reason behind Power Query: Benefits of using age calculation with Power Query is that the calculation is performed when you refresh your report. This is done by using a tool that makes calculation easy and quicker, and there's no extra cost when calculating it with DAX for a measurement of of runtime.
• Other scenarios This is not meant for the calculation of the date of birth. It is possible to calculate the time of inventory on products as well as the different 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 has an BSc with a major of Computer engineering. He has more than 20 years of work experience in the field of data analysis as well as databases, BI developing, and programming mostly using Microsoft technologies. He was a Microsoft Data Platform MVP for nine consecutive years (from 2011, to now) in recognition of his love for Microsoft BI. Reza has been a prolific writer and co-founder at RADACAD. Reza is also the co-founder and co-organizer 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 has written books on MS SQL BI and also is writing various other books. He was also an active forum member on online technical forums like MSDN as well as Experts-Exchange and was moderator of MSDN SQL Server forums and is an MCP and MCSE aswell as an MCITP in BI. He is also the head for 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 completely free and includes over 1700 pages and a second book titled Power BI Pro Architecture published by Apress.
The company 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 goal is to help users find the most effective solutions for data, and he's a Data enthusiast.This post was posted under Power BI, Power BI from Rookie to Rockstar, Power Query and is part of Power BI, Power BI from Rookie to Rock Star, Power Query. The following is an excellent resource for you to bookmark.

Post navigation

Create Different Visual Pages and Share them with different Security Groups. Power BIAge is a Years Calculation , which works for Leap Year in Power BI using Power Query