Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, because DAX is the preferred languagein many studiesin Power BI, many are not aware of the functions available in Power Query. In this blog , I'm going to describe how simple it is to calculateAge in Power BI by using Power BI. This methodis very effective in situations where the Age calculationcan be performed on a calculated row-by row basis.

Calculate Age from a date

This is the DimCustomer table that comes from the AdventureWorksDW table, which has the birthdate column. I've removed some of the columns that aren't needed in order to make it easier to read;

If you wish to calculate the age of every customer the only thing you need do is:

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

That's it. It is possible to calculate an amount that is equal to the sum of the Birthdate column, in addition to the date and time of the present.

However, the age that you see under the Age column, doesn't appear to be an actual age. It's because it's a time.

Duration

Duration is a distinct data type used on Power Query which represents the variations between Two DateTime values. Duration is made up of four different values:

days.hours.minutes.seconds

and that's the way to consider the data above. From the perspective of users, they shouldn't find specifics such as this. There are ways to fetch each part by examining the length. By selecting the Duration menu it will reveal that you can get the number of seconds and minutes, as well as hours, days and years from it.

To utilize for the method that involves calculating the age in years like, for example you simply click on Total Years:

Take note that the duration of the program is calculated by days. This is then divided by 365 days to provide you with the annual value.

Rounding

To conclude, nobody says their aged is 53.813698630136983! They use the term 53, and then round it down. It is possible to select Rounding and Round Down by clicking on the Transform tab.

This will give you an estimate of your age in years:

Then you can tidy the other columns, should you like (or maybe you've applied transformations by using the Transform tab to prevent the column creation) Then name the column Age:

Things to Know

• Refresh The age that is calculated using this method will be refreshed at the time the database refresh occurs. Each time, it will check with the birth date with the date and the date the data refresh took place. In this method will provide an older calculation for age. If you need the calculation of age to be carried out automatically using DAX Here I have explained how to apply.
• How to utilize Power Query: Benefits of making age calculations with Power Query can be that this calculations are made when you refresh your report. This is achieved by making use of an instrument that makes the calculation easy, and there will not be additional work involved in doing it using DAX to measure runtime.
• Other scenarios It is not an accurate method of calculating age by birth date. This is a great way to measure inventory for products as well as for the distinction between the dates of two days or times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc at the level of Computer engineering. More than twenty years' experience in data analysis , database programming, BI, and development predominantly with Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years (from 2011 until now) for his dedication in Microsoft BI. Reza is known as a prolific writer and co-founder of RADACAD. Reza is also 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 has written several works regarding MS SQL BI and also is working on additional. He was also a regular participant in online forums for technical issues like MSDN Experts-Exchange as well as the moderator of MSDN SQL Server forum, as well as holding the MCP and MCSE as well as an MCITP of BI. He is also the leader of the New Zealand Business Intelligence users group. Also, he is the creator of the popularly praised Power BI from Rookie to Rock Star, which is completely free and comprises nearly 700 pages of information and The Power BI Pro Architecture published by Apress.
The speaker is an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, as well as SQL user groups. And He is a Microsoft Certified Trainer.
Reza's dream is to help users to find the most efficient data solution. He is an avid Data enthusiast.This post was released with the title 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 resource for you to bookmark.

Post navigation

Share Different Visual Pages using different security groups in Power to access BIAge's Age Calculation which can be used to calculate Leap Years in Power BI using Power Query --