Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, as DAX can be the largest and most popular language usedin several functionsin Power BI, the majority of users don't recognize the feature available in Power Query. In this article, I'll describe how simple it is to calculateAge in Power BI in conjunction with PowerBI. The methodis extremely helpful in situations where the calculation of the agecan be performed on pre-calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that is part of the AdventureWorksDW table and is an older column. I've removed some of the additional columns to make it simpler to understand.

To calculate the age and gender of every consumer, all you have to do is to:

• In Power BI Desktop, Click on Transform Data
• The Power Query Editor window; start by selecting the Birthdate column.
• Click on the add Column Tab to go under the "From Date & Time" section. And under Date choose the appropriate age range.

That's about it. It can calculate an amount that's the product of the Birthdate column as well as the current date and time.

However, the age appearing within that Age column, does not actually look like an age. This is because it's a duration.

Duration

Duration is a unique type of data format found within Power Query which represents the distinction between two DateTime values. Duration is a mix from four different numbers.

days.hours.minutes.seconds

These are the values you'll discover in these values. From a personal viewpoint, they shouldn't have to search for specifics like the ones listed above. There are ways to get every part of the time. With the Duration menu choice, you'll see the number of seconds, minutes, hours, days and years out of it.

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

The duration is calculated in days . It is then subdivided by 365 in order to get the annual value.

Rounding

Finally, no one claims you are 53.813698630136983! They say 53, with a rounding down. You can easily choose 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's also possible to tidy other columns, If you'd like (or you could have applied transformations from the Transform tab to stop creating new columns) This column can be named. column Age.

Things to Know

• Refresh: The age calculated using the method gets updated every time you are refreshing your dataset. Each time, it'll compare your birthdate with the date and timing at the time of refresh. This method is pre-calculating an age. If you, however, require the calculation of age to be executed dynamically employing DAX, here's how I described an approach you could utilize.
• Arguments for choosing Power Query: Benefits that come with age calculations made using Power Query is that the calculation is carried out at the time of refreshing your report. This is performed by making use of a tool that allows for easier calculation, and eliminates the additional expense of calculating it with DAX to measure runtime.
• Another scenario This is not employed to calculate the age of a person based on their birth date. It could be used for inventory of products , as also for the differences 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 has a BSc of Computer engineering. The engineer has over 20 years' experience in the field of data analysis databases programming, BI, development and programming with a focus upon Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years in a row (from 2011 to now) because of his devotion in Microsoft BI. Reza is a prolific author and co-founder of RADACAD. Reza is also the co-founder and co-organizer for 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 several books about MS SQL BI and also is writing more books. He also was a frequent member of online forums for technical issues such as MSDN and Experts Exchange, as well as moderator for MSDN SQL Server forums, and holds an MCP and an MCSE and MCITP for Business Intelligence. Director of the New Zealand Business Intelligence users group. Also, he's the author of the very well-known workbook 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 an additional book called Power BI Pro Architecture published by Apress.
Speakers are 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 goal is to help you discover the most effective solutions for data, and He's a Data enthusiast.This story was featured in Power BI, Power BI from Rookie to Rockstar, Power Query and related to Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic guide to bookmark.

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