Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. However, because DAX is the preferred languagein numerous analysisin Power BI, many are not aware of the feature available in Power Query. In this post, I'm going to describe how simple to calculateAge in Power BI by using Power BI. This methodis exceptionally efficient in situations where the age calculationcan be made by calculating the row-by-row basis.

Calculate Age from a date

It's the DimCustomer table that comes from the AdventureWorksDW table which includes an age column. I've removed the columns that aren't needed to make it more readable;

If you're trying to calculate the age of each customer, all you have to do is:

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

This is it. It can calculate an amount that is the sum of the column for Birthdate column, and the current date and hour.

However, the age you see under the Age column, doesn't appear to be an actual age. It's because it's a duration.

Duration

Duration is an exclusive data type in Power Query which represents the differentiating characteristics of the two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

and that's the way to interpret the data above. But from a user's point of view, it's not a good idea to seek out details like that. There are methods to locate each of the parts from the length. When you select the Duration menu, it will tell you that you can get the number of seconds, minutes, hours days, and years from it.

To use for the method to use the method of calculating the age in years like, for instance you simply select Total Years.

Make note of the fact that the duration of this program measured by days. This is then divided by 365, to give you the year-long value.

Rounding

At the final point, nobody claims that their aged is 53.813698630136983! They use the term 53, and rounding it down. You can select Rounding and Round Down from the Transform tab.

This will give you the age in years:

Then you can clear the other columns, if you want (or maybe you've applied transformations via the Transform tab to prevent the creation of columns) Name this column: Age:

Things to Know

• Refresh The amount of time calculated through this method is refreshed at the time that your data is refreshed. And each time it will compare with the birth date with the date and the date the data refresh took place. It is a method can be used to calculate age earlier. If you're looking for your calculation to be carried out automatically using DAX I've explained how you can employ.
• How to use Power Query Benefits of calculating age in Power Query will be the calculation occurs at the time of refreshing your report. This is done making use of an instrument that makes the calculation easy, and there will not be additional cost in doing the calculation using DAX to measure of runtime.
• Additional scenarios It is not for calculating age only by birth date. This is an excellent method to assess inventory of product and for the differences between and two date ranges from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc with a degree in Computer engineering. Over 20 years of' experience in data analysis , database, BI, programming, and development predominantly with Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years in a row (from 2011 until today) for his passion for Microsoft BI. Reza has been a prolific writer and co-founder of RADACAD. Reza is also the co-founder and co-organizer of the Difinity conference 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 published a number of books in the field of MS SQL BI and also is working on more. He was also an active forum participant on online forums for technical questions like MSDN Experts-Exchange as well as the moderator for MSDN SQL Server forum and holds the MCP and MCSE as well as an MCITP of BI. He is the director of the New Zealand Business Intelligence users group. Also, he is the creator of the book very well-loved Power BI from Rookie to Rock Star, which is completely free and comprises hundreds of pages worth of content in addition to The Power BI Pro Architecture published by Apress.
Speakers are an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday along with SQL users groups. And He is a Microsoft Certified Trainer.
Reza's aim is to assist users find the most effective data solution. He is a Data enthusiast.This article was released under 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 article is a good reference to save.

Post navigation

- Share Different Visual Pages by utilizing different security groups in Power BIAge's Age Calculation that can be used to calculate Leap Year within Power BI by using Power Query and Power Query.