Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that's a basic method that is a simple method of calculating the age. However, since DAX is the most popular language usedin several calculationsin Power BI, many do not know about the feature available in Power Query. In this article, I'll describe how simple for you to calculateAge using Power BI by using PowerBI. This methodis very beneficial when you have to carry out the age calculationcan be done by calculating the age on a row by row basis.

Calculate Age from a date

This is what's known as the DimCustomer table , which comes an AdventureWorksDW table which functions like the birthday date column. I've eliminated columns that aren't required for clarity;

For you to calculate your age for each client, you'll need:

• In Power BI Desktop, Click on Transform Data
• In the PowerQuery Editor window, ensure that you select the Birthdate column first.
• Select the Add Column Tab and click on the Add Column Tab, which is under the "From Date & Time" section. Under Date, choose Age

That's all there is to it. This calculates the calculate any difference to your Birthdate column, as well as the current date and date.

The age you see when looking on the Age column, doesn't seem like an age. It's because it's a real time period.

Duration

Duration is a special type of data that is used for Power Query which represents the different between two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

And that's exactly what you'll discover in the details above. However, from a users' viewpoint, they shouldn't be required to study information in the same way as those mentioned above. There are ways to get each of the components of the time. Utilizing the Duration menu, you'll see that you can pull the total amount of minutes, seconds, hours, days , and even years from it.

To assist in calculating the age in years such as it is possible to get to Total Year:

The duration is measured in days and then divided into 365 days to produce the annual sum.

Rounding

Truthfully, no one has stated what their child's age as 53.813698630136983! They state 53, which is rounding down. It's easy to choose the Rounding option , and then to round down on the Transform tab.

This will give you the year-old number:

It's then possible to remove other columns if you'd like (or there's a chance that you've used transformations in the Transform tab in order to not create new columns) You can also name this column Age.

Things to Know

• Refresh The age that is calculated this way will get changed at the time of refreshing your data. Every subsequent time, it'll be matched to the birthdate with the date and date that the refresh of data took place. This method is a way to calculate earlier of age. If, however, you want the age calculation to be carried out dynamically with DAX This is what I've shown how to use.
• The rationale behind Power Query: Benefits of performing an age calculation using Power Query is that the calculation takes place each time you refresh your report. It is done using an algorithm which makes the calculation easy to do, and there won't be any additional overhead when calculating it using DAX because it is a way to gauge runtime.
• Different scenarios This is not designed to calculate the age of a person based on their birth date. This could be used to calculate product inventory age and the various dates and dates that differ from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds earned a BSc in Computer engineering. He has more than 20 years of experience in data analysis as well as the BI and databases fields, as well as programming and development generally employing Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011, until now) because of his commitment to Microsoft BI. Reza is a regular blog writerand director and co-founder of RADACAD. Reza is co-founder and organizer of Difinity the conference held located within 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 on MS SQL BI and also is working on other books. He was also a regular participant in online forums for technical issues like MSDN and Experts-Exchange, and was moderator of MSDN SQL Server forums, as well as an MCP and the MCSE, as well as an MCITP in BI. He is the founder of the New Zealand Business Intelligence users group. Additionally, he's an author of the extremely popular the book Power BI from Rookie to Rock Star, which is freely available and contains more than 17000 pages of information and it's the Power BI Pro Architecture published by Apress.
He 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 ambition is to help users find the most effective solutions for data, and he's an avid Data enthusiast.This post was published on the topic of 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 resource to bookmark.

Post navigation

Create Different Visual Pages by using different security groups within Power The BIAge's Calculation of Years that is used for Leap Year in Power BI by using Power Query