Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. However, as DAX is the most popular languagein various studiesin Power BI, many don't have any idea about the option available within Power Query. In this blog I'm going to show you how simple to calculateAge in Power BI by using Power BI. This methodis highly effective when age calculationcan be completed on a calculated row-by row basis.

Calculate Age from a date

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

If you want to calculate the age of each customer, all you have to do is:

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

That's all there is to it. This will calculate an amount that is equal to the sum of the Birthdate column, along with the current date and time.

But, the age you see under the Age column doesn't seem to be an age. It's because it's a time.

Duration

Duration is a distinctive kind of data within Power Query which represents the difference between two DateTime values. Duration is a mix of four different values:

days.hours.minutes.seconds

And that's how you look at the data in the above. But from users' perspective you shouldn't expect them to search for particulars like that. There are methods to fetch each part by examining the length. If you choose the Duration menu it will reveal that you can get the number of seconds, minutes, hours, days, and years from it.

To make use of this method of calculating the age in years like, for instance you can click on Total Years:

Note that the length of the program is measured in terms of days. It is then divided by 365, which will give you the year-long value.

Rounding

To conclude, no one claims their aged is 53.813698630136983! They say 53, with a rounding down. It is possible to select Rounding and Round Down in the Transform tab.

This will give you the number in years:

Then you can clear the other columns, should you like (or maybe you've applied transformations by using the Transform tab to stop the column creation) Also, name this column; Age:

Things to Know

• Refresh The amount of time calculated using the method can be updated during the process of refreshing your database. and each time will compare with the birth date with the date and day on which the refresh of data took place. The method provides an earlier estimation of the age. If you want the calculation of age to take place quickly using DAX I've explained the methods you can use.
• How to utilize Power Query The advantage of calculating age in Power Query can be that this calculation is performed during the refresh of your report. This is done by making use of an instrument which makes the calculation simple, and it won't be extra overhead in calculating the calculation using DAX for determining runtime.
• Additional scenarios It is not an accurate method of calculating age by birthdate. This is an excellent method to establish inventory levels for products and for the different between different dates from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc with a degree in Computer engineering. More than twenty years of experience in the field of data analysis, database, BI, programming and development mostly based focused on Microsoft technologies. He has been a certified Microsoft Data Platform MVP for nine consecutive years (from 2011 until now) due to his commitment to Microsoft BI. Reza is an incredibly prolific writer and co-founder of RADACAD. Reza is also co-founder and director of Difinity conference in 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 couple of book about MS SQL BI and also is writing a few more. He was also a regular member of online forums on technical issues like MSDN and Experts-Exchange . He was the moderator for MSDN SQL Server Forums and is a MCP and the MCSE. He is also an MCITP of BI. He is also the head of the New Zealand Business Intelligence users group. Also, he is the author of the highly praised Power BI from Rookie to Rock Star, which is free and contains greater than 700 pages of information, as well as The Power BI Pro Architecture published by Apress.
He is an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday in addition to SQL user groups. And He is a Microsoft Certified Trainer.
Reza's desire is to help users find the ideal data solution. He is a Data enthusiast.This post was released in Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. This article is a good reference to save.

Post navigation

- Share Different Visual Pages with Different Security Groups in Power to access BIAge's Age Calculation that can be used to calculate Leap Year within Power BI using Power Query and Power Query.