Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, since DAX is the most popular languagein many analysisin Power BI, many are not aware of the function available in Power Query. In this blog I'm going to demonstrate how easy to calculateAge in Power BI, using Power BI. The methodis highly effective when the age calculationcan be made on a previously calculated row-by - row basis.

Calculate Age from a date

This is the DimCustomer table from the AdventureWorksDW table, which has the birthdate column. I've removed a few of the columns that aren't needed to make it more readable;

If you're trying to calculate the age of each customer, all you have to do is to:

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

That's all there is to it. This can calculate an amount equal to the sum of the column for Birthdate column, as well as the current date and time.

But, the age you can see in the Age column doesn't seem to be an actual age. This is because it's a duration.

Duration

Duration is an exclusive data type found inside Power Query which represents the distinctions between the two DateTime values. Duration is a mixture of four different values:

days.hours.minutes.seconds

That's the way you analyze the data you've seen above. But from a user's point of view, you don't want them to find specifics such as that. There are ways to fetch each part in the length. By choosing the Duration menu it will show you can extract the number of seconds and minutes, as well as hours as well as days and years from it.

To utilize for the method using the technique of calculating the age in years like, for instance you can select Total Years.

Make note of the fact that the duration of the program is calculated by days. This is then divided by 365, to calculate the value for the year.

Rounding

The final thing to note is that no one claims their date of birth is 53.813698630136983! They say 53, with the rounding down. Select Rounding and Round Down on the Transform tab for it.

This will give you the number in years:

After that, you can tidy up the other columns, if desired (or perhaps you've created transformations with the Transform tab to stop the column creation) and name this column: Age:

Things to Know

• Refresh: The age calculated using this method will be refreshed when the time comes refresh of your database. Every time, the system will compare your date of birth to the date and the date the data refresh took place. It is a method allows for an earlier age calculation. If you require your calculation to be done by DAX in a way that is dynamically performed DAX I've explained how to use it.
• How to utilize Power Query The benefit of making age calculations with Power Query is that the calculation occurs at the time of refreshing your report. This is done by making use of an instrument that makes the calculation simple, and it won't be extra overhead in calculating the calculation using DAX because it is a measurement of runtime.
• Additional scenarios It is not used to determine age on the basis of birth date. This is a good way to establish inventory levels for products and to determine the difference between two dates or dates each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc with a degree in Computer engineering. There are more than 20 years of expertise in data analysis databases programming, BI and development mostly based with Microsoft technologies. He has been a certified Microsoft Data Platform MVP for nine consecutive years (from 2011 until the present) for his passion for Microsoft BI. Reza is known as a prolific author and co-founder at RADACAD. Reza is also co-founder and co-chairman 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 has written a few publications about MS SQL BI and also is working on several more. He was also an active participant in online forums on technical questions like MSDN and Experts-Exchange . He was the moderator of MSDN SQL Server forum which is where he holds the MCP, the MCSE. He is also an MCITP for BI. He is the Director of the New Zealand Business Intelligence users group. In addition, he's also the co-author of the book highly praised Power BI from Rookie to Rock Star, which is free and includes hundreds of pages of material as well as The 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, along with SQL user groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help users find the best data solution. He is a Data enthusiast.This blog post has been published as part of 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 article is a great guide to bookmark.

Post navigation

Share Different Visual Pages with different Security Groups Power BIAge's Age Calculation that is able to calculate Leap Years in Power BI with Power Query to calculate Leap Year.