Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, since DAX is the most popular languagein several computationsin Power BI, many are unaware of the function available in Power Query. In this article I'll teach you how to calculateAge in Power BI with Power BI. It is a methodis extremely useful when it is necessary to do the calculations of agecan be done as a pre-calculated row and row basis.

Calculate Age from a date

The table below is DimCustomer table that is part of the AdventureWorksDW table which as a birthdate column. I've removed the columns that aren't needed to make it easier to read;

If you want to calculate the actual age of each customer, all you need is to:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; start by selecting the Birthdate column.
• Go to the Add Column Tab, which is under the "From Date & Time" section, and then under Date, choose the age range.

That's it. This can calculate what's the ratio between Birthdate column along with the current date and time.

However, the age appears under the Age column, does not really look like an age. This is because it's a duration.

Duration

Duration is a special data type in Power Query which represents the differences from two DateTime values. Duration is a combination of four values:

days.hours.minutes.seconds

This is how you can read the values above. However, from the perspective of the user it is not expected of them to read details like that. There are methods that can get each portion that is the amount of time. If you click on the Duration menu you'll notice that you can extract the amount of seconds, minutes, hours, days and years out of it.

To assist in calculating the age in years for instance you just need to select Total Years.

Be aware that the duration is calculated in days and then divided by 365, to give you the annual value.

Rounding

Finally, nobody is claiming the age of their child as 53.813698630136983! they say it as 53, and then round it down. You can select Rounding option and then the round down from the Transform tab for it.

This will show you your age in years:

You can then clean the other columns if you'd like (or there is a chance that you have made use of transformations in the Transform tab to prevent creating new columns) And name this column: Age.

Things to Know

• Refresh The age that is calculated this way will be updated at the time of refreshing your dataset. And each time, it will compare your birthdate with the date and the time of the refresh. This method is an algorithm for pre-calculating the age. If, however, you require the calculation to be carried out dynamically, using DAX, here I explained the method you could employ.
• The reason behind Power Query: Benefits of performing an age calculation using Power Query is that the calculation is done during the refresh of your report. The report is refreshed using an algorithm that makes the calculation simpler, and there's no need for the extra expense of doing it with DAX as a measure of runtime.
• Other scenarios It is not meant to calculate age only on the basis of birthdate. It can be used to calculate product inventory age and the difference between two dates and times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc with a major in Computer engineering. He is more than twenty years' experience in data analysis, BI, databases, programmingand development primarily on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine continuous years (from 2011, until now) due to his dedication to Microsoft BI. Reza is a prolific writer and is co-founder with RADACAD. Reza is also co-founder as well as co-organizer of the 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 a number of other books. He was also a regular participant in online forums for technical issues like MSDN and Experts-Exchange and was moderator of MSDN SQL Server forums, and holds the MCP and MCSE as well as an MCITP of BI. He is the founder of the New Zealand Business Intelligence users group. Additionally, he is the creator of the well-known book Power BI from Rookie to Rock Star, which is free and contains more than 170 pages of content as well as the Power BI Pro Architecture published by Apress.
His credentials include being an International Speaker in 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 goal is to help you discover the most effective data solution, he is Data enthusiast.This entry was posted on Power BI, Power BI from Rookie to Rockstar, Power Query and was tagged with 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 Power BIAge's Calculation of Years that is used for Leap Year in Power BI by using Power Query