Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. But, since DAX is the most used language usedin numerous calculationsin Power BI, many are not aware of the functionality available in Power Query. In this blog , I'll explain how easy to calculateAge in Power BI with Power BI. This methodis exceptionally beneficial in situations where you need to calculate your Age calculationcan be carried out on a pre-calculated row by the row.

Calculate Age from a date

Here is the DimCustomer table that is part of the AdventureWorksDW table that serves as the "birthdate" column. I've removed the columns that don't need to be there for ease of read.

For you to calculate your age for each person All you need to do is:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; start by choosing the column titled Birthdate.
• go to Add Column Tab, and select"Add Column", then select the "From Date & Time" section. In the Date, select the age range.

This is it. This doesn't calculate any variation in the Birthdate column as well as the current date and time.

The age that displays in Age column does not appear to be an actual age. This is because it's the term "Duration.

Duration

Duration is a specific kind of data type employed by data type in Power Query which represents the differences between two DateTime values. Duration is a mix with four numbers.

days.hours.minutes.seconds

This is the way to find the above values. However, for users' perspective they shouldn't be expected to search for information like this. There are methods by which you are able to get each part that corresponds to an amount of time. When you select the Duration menu, you'll be able to see how you can determine the duration of seconds to minutes, hours days, and years from it.

To use it for calculating the age in years such as this, it is easy to hit Total Year:

The duration is measured in terms of days, then that divided by the number of days, you will get an annual figure.

Rounding

There is no truth to it, and no one declares that their age is 53.813698630136983! They call it 53, and then round it down. You can select Rounding option and then the round down from the Transform tab.

This will provide you with the age in years:

Clean out the other columns If you want to (or the reason could be that you've used transformations in the Transform tab to avoid creating new columns) You can name this column; Age:

Things to Know

• Refresh The date calculated this way will get updated at the time of refreshing your data. and each time will compare dates of birth as well as the date and time for refresh. This method is an initial calculation of the age. If you require your age calculation to be executed dynamically, using DAX This is the method I've described how to utilize.
• Reasons to choose Power Query: Benefits of making age calculations with Power Query is that the calculation occurs at the time when your report is refreshed. It refreshes the report by using an application that makes calculations more simple, and it's not a cost-plus benefit of formulating it using DAX for measure of time.
• Another scenario It is not intended to calculate age only by birthdate. This is a way to calculate inventory of products and also the difference between two dates, or times, from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc on Computer engineering. He holds the equivalent of more than 20 years of experience in the field of data analysis and programming databases and development, focusing with Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine years (from 2011 until the present) because of his dedication to Microsoft BI. Reza is a frequent blog writerand is the editor and co-founder of RADACAD. Reza is also the co-founder and co-organizer for the Difinity event within 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 books on MS SQL BI and also is working on books on other topics. He was also a regular participant in forums online for technical issues such as MSDN and Experts-Exchange and was the moderator for MSDN SQL Server forums, and holds an MCP and the MCSE, as well as an MSCITP for BI. He is the head of the New Zealand Business Intelligence users group. He is also the writer of the book , which is extremely well-loved Power BI from Rookie to Rock Star, which is free and contains more than 1700 pages worth of information and an additional book called Power BI Pro Architecture published by Apress.
His credentials include being an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, in addition to SQL users groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help users to find the ideal data solution. He is a Data enthusiast.This entry was published as Power BI, Power BI from Rookie to Rockstar, Power Query and is covered with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share different visual pages using different Security Groups Power BIAge's Year Calculation that is used for Leap Year in Power BI with Power Query