Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, because DAX is by far the most widely used language usedin several functionsin Power BI, many people do not fully appreciate the capabilities of Power Query. In this article I will describe how simple it is to calculateAge within Power BI using Power BI. It is a great methodis extremely useful in situations where the calculation of an agecan be completed on a pre-calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table, which is part of the AdventureWorksDW table with an age column. I've removed some of the extra columns and made it simpler to read.

In order to calculate what age each person who purchases from you, all that you need is to:

• In Power BI Desktop, Click on Transform Data
• Within the Power Query Editor window; begin by selecting the column "Birthdate.
• Click on the Add Column Tab to go under the "From Date & Time" section, and under Date Select the age range.

That's about it. It is possible to calculate an amount that is the sum of the column for birthdate, Birthdate column as well as the current date and the time.

However, the age appearing within that Age column, it does not appear to be an actual age. This is because it's an expression of time.

Duration

Duration is a particular kind of data structure that can be found in Power Query which represents the differences between two DateTime values. Duration is a mixture of 4 numbers.

days.hours.minutes.seconds

It's exactly what you'll find in the values above. In reality, from an individual's standpoint, they shouldn't be required to seek out specifics such as those mentioned above. There are methods that could make sense of all the time. using the Duration menu option you'll be able to see the range of seconds to minutes, hours, days, and years out of it.

For calculating the age in years such as, for instance you just need to go through Total Years.

The duration is calculated in days and then divided by 365 to give you the value of the year.

Rounding

No one has claimed they are 53.813698630136983! They claim it's 53, but with a rounding down. You can select the rounding option, and then round down the Transform tab.

This will give you an indication of the age you've reached in your years.

It is then possible to cleanse other columns, if desired (or you might have utilized transformations from the Transform tab to prevent the creation of new columns) The column could be named column: Age.

Things to Know

• Refresh The age that is calculated using the method can be refreshed every time you are refreshing your data. Each time, it compares your birthdate with the date and date at the time of refresh. This method is pre-calculating an age. If, however, you want the age calculation to be dynamically performed, by using DAX, here's the way I described an approach you can make use of.
• Reasons to choose Power Query: Benefits of doing age calculations using Power Query is that the calculation is made when you refresh your report. This is performed by making use of a program that makes calculation easier, and there's no need to spend the expense of calculating the calculation using DAX as a measure of running time.
• Another scenario This is not utilized to calculate the age of a person based on their birth date. This could be used for inventory of products as or the difference in two dates and dates from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc of Computer engineering. Over 20 years' experience in data analysis , database, BI, programmingand development specifically focused around Microsoft technologies. He is an official Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) because of his dedication in Microsoft BI. Reza is known as a prolific writer and is co-founder with RADACAD. Reza is also co-founder and director of Difinity Conference at 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 some books on MS SQL BI and also is working on other books. He was also a frequent participant in online forums on technical questions like MSDN and Experts-Exchange , as well as moderator for MSDN SQL Server forums, and is an MCP , MCSE as well as an the MCITP for Business. The director is of the New Zealand Business Intelligence users group. Additionally, he is the writer of the incredibly popular books such as Power BI from Rookie to Rock Star, which is offered for free and contains more than 17000 pages worth of information. There's also another book called Power BI Pro Architecture published by Apress.
This speaker has been an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, along with SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help you find the most efficient ways to use data, and he's a Data enthusiast.This article was published in Power BI, Power BI from Rookie to Rockstar, Power Query and closely related to Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great resource to save to your bookmarks.

Post navigation

- Share different visual pages by using Different Security Groups that PowerBIAge uses for Year Calculation that works for Leap Year in Power BI using Power Query