Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, since DAX is the most popular language usedin several calculationin Power BI many are unaware of this feature in Power Query. In this blog post I will discuss how easy it is to calculateAge in Power BI with Power BI. The methodis extremely beneficial in cases where the calculation of an agecan be completed in a row-by-row basis.

Calculate Age from a date

That's the DimCustomer table that is part of the AdventureWorksDW table which contains a birthdate column. I've removed some the columns that aren't required for clarity;

For you to calculate an age for every buyer, you will need:

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

It's that simple. it. This is how you calculate the amount which is the total from the Birthdate column along with the current date and the time.

However, the date that appears within the Age column does not look like an age. That is because it is an actual duration.

Duration

Duration is a distinct form of data that is utilized to calculate the duration of a query in Power Query which represents the variation between the two DateTime values. Duration is the mixture of four numbers:

days.hours.minutes.seconds

This is how you read the above values. But from one's own perspective, you don't need them to search for details like this. There are methods that can find each part that represents equivalent to the duration. When you select the Duration menu you'll discover that you'll be able to get the amount of seconds , minutes as well as days, hours and years out of it.

Help in calculating the age in years such as, for instance it is simple to choose Total Years.

It is important to note that the length of your program will be measured in days , then subdivided into 365 to provide you with the value for the year.

Rounding

In the end, nobody says they're 53.813698630136983! They call it 53, and then round it down. It's easy to choose Rounding and Round Down under the Transform tab.

This will provide you with the age in years:

It's also possible to cleanse other columns if you'd like (or maybe you've made use of transformations in the Transform tab to prevent creating of additional columns) This column can be changed to an age column; Age:

Things to Know

• Refresh The age that is calculated using this method will be updated each time you are refreshing your database. and each time will compare the birthdate with the date and time at which the refreshed. This method is an initial calculation of the age. If you need this calculation performed dynamically using DAX here I described a method that you could use.
• The reason for Power Query: Benefits of doing calculations on age in Power Query is that the calculation is done during the refresh of your report. You use a tool that makes the calculation easier, and there's no cost associated with using DAX for calculating the runtime.
• Another case where these are not in use to calculate age, only beginning with the date of birth. This can be used to determine the age of inventory of products as well as for the variation among two dates each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc of Computer engineering. He has the more than 20 years of knowledge in the field of data analysis as well as BI, databases development and programming with a focus using Microsoft technologies. He was a Microsoft Data Platform MVP for 9 consecutive years (from 2011 to the present) due to his dedication in the field of Microsoft BI. Reza remains an active writer and co-founder at RADACAD. Reza is also co-founder as well as coordinator of Difinity Conference which is held 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 some publications about MS SQL BI and also is working on a few more. The author was a regular forum member on technical forums on the internet like MSDN or Experts-Exchange and was also the moderator for MSDN SQL Server forums, and is an MCP or MCSE. He also holds the MCITP for Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. In addition, he is the creator of the popular publication Power BI from Rookie to Rock Star, which is completely free and includes more than 170 pages of information and part of the 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 User Groups. And He is a Microsoft Certified Trainer.
Reza's main goal is helping users find the right data solution. He's a Data enthusiast.This post was uploaded within Power BI, Power BI from Rookie to Rockstar, Power Query and is filed with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

Share visual pages on various security groups inside Power BIAge in Years Calculation which is able for calculate Leap Year in Power BI using Power Query