Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, because DAX is the most popular language usedin several calculationsin the Power BI platform, many don't know about this function in Power Query. In this blog post I will explain how easy to calculateAge in Power BI with Power BI. This methodis extremely helpful for situations in which the calculation of the agecan be carried out on an earlier calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table from the AdventureWorksDW table which contains the birthdate column. I've removed some the columns that don't need to be there to make it more readable;

For you to calculate the exact age each buyer, you need is:

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

This is all there is to it. It will calculate the amount which is the total that is the sum of Birthdate column and also the current date and time.

However, the age which appears within the Age column, does not necessarily appear as an age. This is because it's an actual length.

Duration

Duration is a distinct form of data that is utilized by Power Query which represents the differences between the two DateTime values. Duration is the combination of four numbers:

days.hours.minutes.seconds

This is how you see the above data. However, for one's own perspective, you don't want them to look up data like this. There are methods that can make each of the portions that are the amount of time. If you select the Duration menu you'll notice that you can extract the number of seconds, minutes in addition to days, hours, and years out of it.

In order to aid with calculating the age in years such as, say, it is simple to choose Total Years.

Note that the length that the programme runs is calculated in days , and then divided into 365, providing you with the annual value.

Rounding

In the end, nobody says they are 53.813698630136983! They use the term 53, and then then round it down. It is easy to select the Rounding option and Round Down on the Transform tab.

This will give you the age in years:

It's also possible to cleanse other columns if you'd like (or perhaps you've taken advantage of transformations within the Transform tab to prevent formation of new columns) This column can be renamed as Age column; Age:

Things to Know

• Refresh The age that is calculated in this manner will be updated every time you refresh your database. Every time, the database will be compared the birthdate to the date and time that the database refresh was made. This method is an earlier calculation of the age. If you require the calculation to be done dynamically with DAX, I've provided a method you could use.
• The motivation for Power Query: Benefits of doing calculations on age in Power Query is that the calculation is carried out during the refresh of the report. The report is refreshed using tools that make the calculation much easier, and there's no additional expense of using DAX to determine the time of runtime.
• Another scenario These are not in use to calculate age, only beginning from the birth date. This can be used to determine the time of inventory on products as well as for the variation from two days or months 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. He holds an extensive 20-year of working experience in data analysis and BI, database development and programming primarily on Microsoft technologies. He has been an Microsoft Data Platform MVP for nine consecutive years (from 2011 to the present) for his commitment towards Microsoft BI. Reza is an avid writer and co-founder of RADACAD. Reza is co-founder and coordinator of Difinity Conference which is held 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 also written a few books regarding MS SQL BI and also is working on other books. Also, he has been a regular member of online technical forums like MSDN and Experts-Exchange and was also the moderator on the MSDN SQL Server forums, and holds the MCP in addition to MCSE, MCP, and MCITP for BI. He is the creator of the New Zealand Business Intelligence users group. Additionally, he's the creator of the well-known text Power BI from Rookie to Rock Star, which is available for free and has more that 170 pages of information as well as being integral part of Power BI Pro Architecture published by Apress.
It is an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help users discover the most efficient information solution. He is Data enthusiast.This article was published in Power BI, Power BI from Rookie to Rockstar, Power Query and is listed under Power BI, Power BI from Rookie to Rock Star, Power Query. You can follow any comments to this entry through the RSS feed.

Post navigation

Share different visual pages with different security groups in Power BIAge in Years Calculation which can be used to calculate Leap Year in Power BI through Power Query