Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, as DAX is the most widely used language usedin numerous calculationsin Power BI, a lot of people are not aware of the functionality available in Power Query. In this blog post, I'll discuss how easy to calculateAge in Power BI with Power BI. This methodis highly beneficial in situations where your calculations of agecan be made using a pre-calculated row rows.

Calculate Age from a date

This is the DimCustomer table from the AdventureWorksDW table that serves as the "birthdate" column. I've removed columns that don't have any value in order to make it easier to study.

To calculate your age for each person all you have to do is to:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; begin by selecting the Birthdate column.
• click on the Add Column Tab, and select"Add Column", then select the "From Date & Time" section. Then, under Date, select the appropriate age.

That's it. this does calculate any difference from the Birthdate column and also the current date as well as time.

However, the age that can be seen in the Age column, does not really look like an age. That is because it is a Duration.

Duration

Duration is a particular kind of data types used as a the data type used in Power Query which represents the difference between the DateTime and DateTime values. Duration is the result with four numbers.

days.hours.minutes.seconds

This is how you understand the above numbers. However, for users' perspective they shouldn't be expected to locate information like that. There are many ways that can obtain every segment that represents durational. If you click on the Duration menu you'll see that you can get the quantity of seconds to minutes, hours days, and years.

For calculating the age in years for instance, it's easy to hit Total Year:

The duration is measured by days. Then, after that is divided by the amount of days, you will get an annual figure.

Rounding

In reality, no one says the age of their person is 53.813698630136983! They call it 53, and then round it down. You can choose Rounding and then select the round down option from the Transform tab for it.

This will give you the number in years:

Clean out the other columns If you want to (or the reason could be because you utilized transformations using the Transform tab to avoid making new columns) You can name this column"Age:

Things to Know

• Refresh The date calculated this way will be updated at the time of refreshing your dataset. Every time it will be able to compare dates of birth and the time and date for the refresh. This method is the initial calculation of age. If you need the calculation of age to be automatically done with DAX This is the way I've explained in detail how to make use of.
• Why Power Query: Benefits of using age calculations in Power Query is that the calculation is performed at the time that your report is refreshed. The report is refreshed with an application that makes calculations much simpler, plus there is no additional cost to formulating it using DAX for an indicator of runtime.
• Another situation is that it isn't intended to calculate age solely based on birthdate. This is a way to calculate the inventory of goods in addition to the variation between two dates or times one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He earned a BSc from Computer engineering. He has an impressive 20+ years' knowledge of data analysis and programming databases, and development predominantly using Microsoft technologies. He was an official Microsoft Data Platform MVP for nine years in a row (from 2011 to present) due to his commitment towards Microsoft BI. Reza is a regular blog writerand is the editor and co-founder of RADACAD. Reza is co-founder and organizer of Difinity event located on the island of New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote several books on MS SQL BI and also is working on books on other topics. He was also a frequent forum participant on online forums for technical issues like MSDN and Experts-Exchange, and was the moderator for MSDN SQL Server forums, and is an MCP as well as MCSE as well as an MSCITP for BI. He is the head for the New Zealand Business Intelligence users group. He is also the creator of the loved by many. Power BI from Rookie to Rock Star, which is free and contains over 1700 pages and a second book called Power BI Pro Architecture published by Apress.
His credentials comprise of 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 main goal is helping users find the best data solution. He is a Data enthusiast.This entry was posted as Power BI, Power BI from Rookie to Rockstar, Power Query and is listed with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share different visual pages with different Security Groups Power BIAge's Years Calculation, which works for Leap Year in Power BI using Power Query