Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way that can be used for calculating the age. But, since DAX is the preferred languagein many testsin Power BI, many do not know about the option available within Power Query. In this blog , I'm going to explain how easy it is to calculateAge in Power BI with the help of Power BI. This methodis highly effective when the age calculationcan be made by calculating the row-by-row basis.

Calculate Age from a date

That's the DimCustomer table from the AdventureWorksDW table which as an age column. I've removed the extra columns in order to make it easier to read;

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

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

This is it. It is possible to calculate an amount that is the sum of the column for Birthdate column, in addition to the date and time of the present.

However, the age that you can see in the Age column, doesn't appear to be an actual age. It's because it's not a period.

Duration

Duration is a distinctive data type found on Power Query which represents the distinctions between Two DateTime values. Duration is a mix of four different values:

days.hours.minutes.seconds

And that's how you look at the data in the above. But from a user's point of view, you shouldn't expect them to find specifics such as that. There are ways to extract each element of the duration. By choosing the Duration menu it will tell you that you can calculate the number of seconds days, minutes days, years, and seconds from it.

For an method that involves calculating the age in years like, for instance, you can simply click on Total Years:

Note that the length of this program calculated in terms of days. It is then divided by 365, which will yield the annual amount.

Rounding

At the final point, nobody claims that their years of age are 53.813698630136983! They call it 53, and then round it down. It is possible to select Rounding and Round Down from the Transform tab for it.

This will show you your age in years:

Then you can clear the other columns, if you want (or maybe you've applied transformations using the Transform tab to prevent the creating columns) Then name the column as Age:

Things to Know

• Refresh The amount of time calculated using this method is refreshed at the time the database refresh occurs. Each time, it will match with the birth date with the date and the date the data refresh occurred. The method allows for an earlier age calculation. If you're in need of that calculation to be carried out quickly using DAX Here's a detailed explanation of how to use it.
• How to use Power Query Benefits of calculating age in Power Query are that the calculation is performed in the course of updating your report. This is achieved by using an instrument that makes the calculation more simple, and it won't any extra work to calculate it with DAX in order to determine runtime.
• Additional scenarios It is not used to determine age by birth date. This is an excellent method to assess inventory of product and to determine the difference between the dates of two days or times one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc with a degree in Computer engineering. The engineer has over 20 years of experience in data analysis , database programming, BI, and development predominantly in Microsoft technologies. He has been a certified Microsoft Data Platform MVP for nine years (from 2011 until the present) because of his dedication to Microsoft BI. Reza has been a prolific writer and co-founder of RADACAD. Reza is also co-founder and co-chairman 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 written a few ebooks in the field of MS SQL BI and also is writing a few more. He was also an active participant in forums online for technical issues such as MSDN and Experts-Exchange . He was the moderator for MSDN SQL Server forums, with the MCP as well as the MCSE. He also holds an MCITP of BI. He is also the coordinator of the New Zealand Business Intelligence users group. The group is also writer of the book very well-loved Power BI from Rookie to Rock Star, which is free and includes greater than 700 pages of content in addition to The Power BI Pro Architecture published by Apress.
The speaker 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 goal is to help users find the most effective data solution. He is an avid Data enthusiast.This report 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 a fantastic source to bookmark.

Post navigation

Share different visual pages with Different Security Groups in Power to access BIAge's Age Calculation that can be used to calculate the leap year in Power BI by using Power Query (see below).