Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, as DAX is the most popular languagein many analysisin Power BI, many don't know about the functions available in Power Query. In this blog , I'm going to show you how simple it is to calculateAge in Power BI using Power BI. This methodis highly effective when the age calculationcan be performed using a row-by-row basis.

Calculate Age from a date

It's the DimCustomer table in the AdventureWorksDW table, which has the birthdate column. I've removed some of the columns that aren't needed to make it easier to read;

If you wish to calculate the age of every customer the only thing you need do is:

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

That's all there is to it. It can calculate an amount which is the sum of the column for Birthdate column, as well as the current date and time.

However, the age you can see in the Age column doesn't seem to be an actual age. It's because it's a time.

Duration

Duration is a distinct data type that is found within Power Query which represents the differentiating characteristics of the two DateTime values. Duration is a combination of four different values:

days.hours.minutes.seconds

This is how you take the information above into consideration. From the perspective of users, it's not a good idea to look up information like this. There are methods to find each component of the duration. When you select the Duration menu, it will display that you can calculate the number of seconds minutes, hours days, and years from it.

To utilize this method for calculating the age in years like, for example you just select Total Years.

Note that the length of this program measured in days , then divided by 365 to provide you with the annual value.

Rounding

In the end, nobody claims that their aged is 53.813698630136983! They call it 53, and then round it down. It is possible to select Rounding and Round Down on the Transform tab for it.

This will provide you with the age in years:

You can then tidy up the other columns, if you want (or maybe you've made a transformation by using the Transform tab to prevent the column creation) Name this column: Age:

Things to Know

• Refresh The age determined using the method is refreshed when the time comes that your data is refreshed. Each time, it will check your date of birth to the date and time on which the refresh of data took place. The method allows for an earlier age calculation. If you want that calculation to be done in a dynamic manner using DAX Here I have explained the methods you can use.
• How to utilize Power Query The benefit of calculating age in Power Query are that the calculation takes place when you refresh your report. It is accomplished by making use of an instrument which makes the calculation easy, and there will not be any additional overhead when calculating it with DAX to measure of runtime.
• Other scenarios It is not to calculate age solely by birth date. This is a great way to establish inventory levels for products and to determine the difference between the dates of two days or times 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 in Computer engineering. More than twenty years of experience in the field of data analysis, database programming, BI and development primarily based using Microsoft technologies. He has been a certified Microsoft Data Platform MVP for nine consecutive years (from 2011 until now) for his commitment to Microsoft BI. Reza has been a prolific writer and co-founder of RADACAD. Reza is also the co-founder and organizer of the 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 has written a couple of publications about MS SQL BI and also is working on a few additional. He was also a regular member of online forums on technical issues such as MSDN and Experts-Exchange . He was the moderator for MSDN SQL Server forum, and is a MCP and MCSE , as well as an MCITP in BI. He is also the Director of the New Zealand Business Intelligence users group. Also, he is the author of the book highly praised Power BI from Rookie to Rock Star, which is free and has over 700 pages of information and 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, as well as SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help users find the most effective data solution. He is an avid Data enthusiast.This piece was released in 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 source to save.

Post navigation

Share different visual pages using different security groups in Power BI's BIAge Calculation which can be used to calculate the Leap Year in Power BI by using Power Query --