Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method in calculating the age. However, because DAX is the most popular language usedin numerous analysesin Power BI, many do not know about this option within Power Query. In this blog post I will illustrate how easy it can be to calculateAge in Power BI in conjunction with PowerBI. The methodis extraordinarily useful in situations where you need to calculate the estimation of age.can be performed on an earlier calculated row by row basis.

Calculate Age from a date

Here's the DimCustomer table that comprises the AdventureWorksDW table which contains the column with the birth date. I've removed the extra columns to make it easier to read;

If you'd like to calculate an age range for each customer all you'll need is to:

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

That's about it. This is how you calculate any variation within the Birthdate column, as well as the current date and hour.

However, the date that is displayed under"Age", however, the age appears under Age column, and doesn't seem to be a real age. That is because it is an actual Duration.

Duration

Duration is a special type of data of the data type used of the type 'data' in Power Query which represents the differences between two DateTime values. Duration is made up with four values:

days.hours.minutes.seconds

That's how you'll read the info above. However, from a user's viewpoint, don't think that they can read specifics such as the ones listed above. There are different ways to determine each component of the time. If you select the Duration menu you will find that you are able to extract the amount of seconds and minutes in addition to hours, days as well as years from it.

To assist you when calculating the age in years such as this, simply hit Total Year:

Note that the duration is calculated using days. It is after that divided by the number of days for a yearly value.

Rounding

At the final point, nobody declares their age to be 53.813698630136983! The number is 53. later rounded down. It's simple to select Rounding and round down from your Transform tab.

This will show you an age range in years:

Then , you can clean up the other columns you'd prefer to (or you could have used transforms within the Transform tab to avoid making new columns) You can also name this column Age.

Things to Know

• Refresh The age that is calculated this way will be updated when the time comes of refreshing your database. And each time, it compares the date of birth to the date and time for the refreshing. This method is an earlier calculation of the age. If you require the calculation be made dynamically using DAX This is what I have described how to utilize.
• What's the reason? Power Query: Benefits of the age calculation feature in Power Query is the fact that it is executed whenever you refresh the report. The calculation is carried out using an algorithm that makes the calculation much quicker and easier, and there is no extra cost to calculate it with DAX in order to determine of runtime.
• Alternative scenarios It's not to calculate age from the birthdate. It can be used to determine the age of inventory in the case of products as well as the difference of two dates, or dates from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He is a graduate of an BSc from Computer engineering. He has over twenty years of expertise in data analysis database programming, BI, and development mostly with Microsoft technologies. Reza is an official Microsoft Data Platform MVP for nine consecutive years (from 2011 to today) because of his dedication toward Microsoft BI. Reza is an accomplished journalist and co-founder for RADACAD. Reza is also co-founder as well as organizer of Difinity event as well as the Difinity conferences 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 a number of books on MS SQL BI and also is writing a few additional. He is also a frequent forum participant on online forums for technical issues such as MSDN and Experts-Exchange as well as the moderator for MSDN SQL Server forums, and is a holder of MCP, MCSE and the MCP, MCSE, and the MCITP for BI. He is also the director of the New Zealand Business Intelligence users group. He is also the author of the highly acclaimed guidebook Power BI from Rookie to Rock Star, which is completely free and offers more than 700 pages of information and The Power BI Pro Architecture published by Apress.
He is an International speaker in 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 passion is to help users find the most efficient information solution. He is a Data enthusiast.This post was published under Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great source to bookmark.

Post navigation

Share various visual pages by using Different Security groups in Power BI's Year Calculation. This is used for Leap Year in Power BI by using Power Query