Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, because DAX can be the largest and most commonly used language usedin several functionsin Power BI, the majority of users don't fully appreciate the capabilities of Power Query. In this article I will explain how easy it is to calculateAge within Power BI using Power BI. The methodis very beneficial for situations in which the computation of an agecan be carried out on a pre-calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table that can be found in the AdventureWorksDW table with an aged column. I've removed some extra columns so that it is simpler to understand.

To calculate the age of every customer, the only thing you need is to:

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

This is all there is to it. This will calculate an amount that is the sum of the column for birthdate, Birthdate column and the actual date as well as time.

However, the appearance of the age within it's Age column, and does not seem to look like an age. This is because it's an actual duration.

Duration

Duration is a special kind of format for data in Power Query which represents the differences in two DateTime values. Duration is made up from four different numbers.

days.hours.minutes.seconds

These are the values you'll find in the values above. In reality, from an individual's view, they shouldn't need to search for specifics like the ones above. There are ways to find every moment of time. With the Duration menu choice, you'll be able to see the range of seconds, minutes, hours, days, and years out of it.

For calculating the age in years such as, for instance it is as simple as going on to Total Years.

The duration is determined in days and then divided by 365 to give you the yearly value.

Rounding

Finally, no one claims they are 53.813698630136983! They state it as 53, but with a rounding down. You can choose the rounding option, and then round down on the Transform tab.

This will tell you the age you've reached in your years.

It's also possible to cleanse other columns, If you'd like (or you could have utilized transformations within the Transform tab to stop creating new columns) The column could be named column Age.

Things to Know

• Refresh The estimated age using the method is updated every time you refresh your database. Every time, it'll compare your birthdate with the date and time at the moment of refresh. This method is a pre-calculation of an age. If you require the calculation of age to be performed dynamically, with DAX, this is how I described a way that can be used.
• Reasons to choose Power Query: Benefits of doing calculations on age using Power Query is that the calculation is done during the refresh of your report. This is done making use of a program that makes calculations easier, and there's no need for the additional expense of calculating the calculation using DAX to measure time.
• Another scenario is that this isn't employed to calculate the date of birth as the age. It could be used for the inventory of items as well as to determine the difference between two dates or times 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' working in data analysis database, BI, programmingand development mostly focused upon Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years in a row (from 2011 until today) because of his dedication to Microsoft BI. Reza is a prolific author and is co-founder with RADACAD. Reza is also the co-founder and coordinator 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 several books about MS SQL BI and also is writing other books. He was also a regular participant in online forums on technical matters like MSDN and Experts Exchange, as well as moderator of MSDN SQL Server forums, and is an MCP and the MCSE, as well as the MCITP for BI. As the group's director, he is responsible for the New Zealand Business Intelligence users group. Also, he's the author of the highly popular title Power BI from Rookie to Rock Star, which is available for download for free and includes more than 17000 pages worth of information. There's also another book titled 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 goal is to help you discover the most effective solutions for your data. He's a Data enthusiast.This article was published in Power BI, Power BI from Rookie to Rockstar, Power Query and associated with Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great resource for you to bookmark.

Post navigation

- Share various visual pages through Different Security Groups in Power BIAge's Year Calculation that works for Leap Year in Power BI using Power Query