Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method in calculating the age. However, as DAX is the most popular languagein numerous analysisin Power BI, many don't have any idea about the function available in Power Query. In this post, I'm going to describe how simple it is to calculateAge in Power BI using Power BI. The methodis extraordinarily effective when Age calculationcan be done on a calculated row-by row basis.

Calculate Age from a date

The table is DimCustomer table that comes from the AdventureWorksDW table which as the birthdate column. I've removed some of the extra columns so that it is easier to read;

If you wish to calculate the number of years of age for each customer the only thing you need to do is to:

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

That's it. This can calculate an amount equal to the sum of the column for Birthdate column, along with the current date and time.

But, the age you can see in the Age column, doesn't appear to be an actual age. It's due to the fact that it's a length.

Duration

Duration is a unique data type found by using Power Query which represents the differentiating characteristics of the two DateTime values. Duration is a mix of four different values:

days.hours.minutes.seconds

That's the way you analyze the data you've seen above. From the perspective of users, you don't want them to look up information like this. There are ways to locate each of the parts in the length. By choosing the Duration menu it will reveal that you can find the amount of seconds and minutes, as well as hours days, and years from it.

To apply to use the method for calculating the age in years such as you need to choose Total Years.

It is important to note that the length of your program will be measured in terms of days. It is then divided by 365, to provide you with the annual value.

Rounding

To conclude, there is no consensus that says that the aged is 53.813698630136983! They say 53, and rounding it down. Select Rounding and Round Down in the Transform tab.

This will give you the age in years:

After that, you can tidy up the other columns, should you like (or maybe you've made a transformation through the Transform tab to stop the new columns) Name this column with the following: Age

Things to Know

• Refresh The age that is calculated through this method can be updated when the time comes of refreshing your database. and each time will compare dates of birth with the date and time on which the data refresh was made. This method can be used to calculate age earlier. If you're looking for that calculation to be carried out automatically using DAX I've explained how to use it.
• How to utilize Power Query Benefits of calculating age in Power Query can be that this calculation takes place in the course of updating your report. This is done by making use of an instrument that makes the calculation simple, and there won't be any additional overhead when calculating the calculation using DAX as a measure of runtime.
• Additional scenarios an accurate method of calculating age by birth date. This is a great way to assess inventory of product and for the different between and two date ranges 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' experience in the field of data analysis, database programming, BI and development that is primarily in Microsoft technologies. He has been a certified Microsoft Data Platform MVP for nine consecutive years (from 2011 until the present) for his dedication in Microsoft BI. Reza is also a prolific writer and co-founder at RADACAD. Reza is also co-founder and coordinator of 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 published a number of publications about MS SQL BI and also is writing a few more. He was also a regular participant in online forums for technical issues like MSDN and Experts-Exchange and was the moderator for MSDN SQL Server Forums, as well as holding the MCP and MCSE certifications as well as an MCITP in BI. He is the Director of the New Zealand Business Intelligence users group. In addition, he's also the creator of the book that is popularly praised Power BI from Rookie to Rock Star, which is gratis and contains nearly 700 pages worth of content along with The Power BI Pro Architecture published by Apress.
He is 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 ambition is to help users find the ideal data solution. He is a Data enthusiast.This piece was published within 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 an excellent resource to save.

Post navigation

Share Different Visual Pages with different Security Groups Power the BIAge's age Calculation that is able to calculate Leap Year within Power BI with Power Query to calculate Leap Year.