Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that's a basic method of calculating the age. However, as DAX is the largest and most used language usedin several calculationsin Power BI, many do not know about the functionality available in Power Query. In this blog post, I'll detail how simple it is to calculateAge using Power BI by using PowerBI. It is a methodis very useful in situations where it is required to perform the age calculationcan be done using an earlier calculated row by row basis.

Calculate Age from a date

The table is called one of DimCustomer table . It comes an AdventureWorksDW table that serves as the column for birth dates. I've removed columns that don't need to be there to make it more readable;

For you to calculate your age for every purchaser, you must have:

• In Power BI Desktop, Click on Transform Data
• In the PowerQuery Editor window, ensure that you select the Birthdate column first.
• Go to the add Column Tab that is found under the "From Date & Time" section. Under Date, choose Age

That's about it. It will calculate any change to the Birthdate column, as well as the current date and time.

However, the age appears when you look at the Age column, doesn't seem like an actual age. This is because it's actually Duration.

Duration

Duration is a special type of data that is used to calculate the duration of a query in Power Query which represents the variation between the two DateTime values. Duration is a combination of four values:

days.hours.minutes.seconds

and that's what you'll read in the data above. However, from a users' standpoint, they shouldn't have to go and read the information as detailed in the above. There are ways to get each of the components of the time. When you use the Duration menu it will show you how you can take the value of minutes, seconds, hours, days and years out of it.

To assist in calculating the age in years such as it is possible for you to reach Total Year:

The duration is measured in days , and then divided in 365 for the year to get the total.

Rounding

In reality, no one states an age for their children as 53.813698630136983! They say 53, which is rounded down. It's simple to select the Rounding option , and then to round down in the Transform tab for it.

This will give you the number of years:

It's then possible to clean other columns if you'd like (or there's a chance that you have applied transformations through the Transform tab to avoid the need to create new columns) Then, you can name this column Age.

Things to Know

• Refresh The age calculated in this manner will be altered when refreshing your database. Each subsequent time, it'll be matched to the birthdate with the date and day that the refresh of data took place. This method is an earlier calculation of age. However, if you would like the calculation of age to be performed dynamically by using DAX This is the approach I've described how to use DAX.
• The motivation behind Power Query: Benefits of performing an age calculation using Power Query is that the calculation happens each time you refresh your report. The calculation is made using an algorithm which makes the calculation straightforward, and there will not be extra overhead in calculating the age using DAX as a measure of runtime.
• Different scenarios. This isn't designed to calculate the age of a person based on their birth date. It could be used to determine inventory of products and various dates and dates that differ from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has earned an BSc and MSc Computer engineering. He have more that 20 years of experience in data analysis , BI, databases, programming and development, mostly with Microsoft technologies. He was a Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) for his dedication in Microsoft BI. Reza is a frequent blog writer, and also the director and co-founder of RADACAD. Reza is also the co-founder and coordinator of Difinity the conference which takes place 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 number of books about MS SQL BI and also is writing other books. He was also a regular member of online forums on technical issues , such as MSDN and Experts-Exchange, and was moderator on the MSDN SQL Server forums, and is an MCP , Microsoft Certified Specialist (MCSE) as well an MCITP in BI. He is the founder of the New Zealand Business Intelligence users group. He's also an author of the extremely popular workbook Power BI from Rookie to Rock Star, which is available for free and has more that 1700 pages of content and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
The speaker is an International speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL Users Groups. And He is a Microsoft Certified Trainer.
Reza's ambition is to help you discover the right solutions for your data needs, and he's an avid Data enthusiast.This post was posted within 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 good resource for you to bookmark.

Post navigation

Share Different Visual Pages with Different Security Groups within Power the BIAge's Calculation of Years that is used for Leap Year in Power BI by making use of Power Query