Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, because DAX is the primary language usedin many calculationsin Power BI, many do not realize this feature that is available in Power Query. In this article, I'll describe how easy to calculateAge in Power BI using PowerBI. It is a methodis extremely efficient in cases where the estimation of agecan be calculated on a pre-calculated row and the row basis.

Calculate Age from a date

Below, you'll find the DimCustomer table which is part of the AdventureWorksDW table, which includes the birthdate column. I've removed some of the columns that aren't needed in order to make it simpler to comprehend;

To calculate the actual the age for each purchaser the following information is required:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; start by selecting the column for Birthdate.
• click on the Add Column Tab, then click on"Add Column" and then on "From Date & Time" section. And under Date select the age range.

That's all there is. This is how you calculate an amount that is the sum of the Birthdate column, and the time and date column.

However, the number of years as a number in the Age column, does not really appear to be an age. It's because it's an actual Duration.

Duration

Duration is an exclusive type of data format used in Power Query which represents the distinctions between two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

This is how you interpret the numbers above. However, from the point of view of the user it is not required of them to be able to interpret such information. There are methods by which you are able to get each section of the duration. By using the Duration menu, you'll notice that you can remove the amount of seconds as well as minutes, hours months and days from it.

For help calculating the age in years through an example, it is easy to click on Total Years:

The duration is measured in days , and then divided by 365 to provide the annual amount.

Rounding

Finally, no one declares your age as 53.813698630136983! They call it 53, which is the number rounded down. It is possible to select Rounding and Round Down by clicking on the Transform tab for it.

This will show you how old you are:

Then, you can clean the other columns, should you like (or the reason could be that you have used transformations in the Transform tab to stop creating new columns) This column can be named column Age:

Things to Know

• Refresh The data's age that is calculated through this method will be refreshed at the time of refreshing your database. Every time the system is competent to match the birthdate with the date as well as the duration of refreshing. This method can be described as an algorithm to calculate the age. If you'd like to have your age calculation to be performed dynamically with DAX Here is the procedure I have described the method I would recommend making use of.
• The motive behind Power Query: Benefits of using age calculations with Power Query is that the calculation is performed during the process of refreshing your report. It is accomplished by using the power of a tool that makes calculation more efficient and speedier, and there's not any additional expense in the calculation using DAX as a measure of runtime.
• Other scenarios These are not intended to be used to calculate the date of birth. It is possible to calculate the age of inventory of items as well as the different dates and dates with respect to each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds an BSc with a major with a concentration in Computer engineering. The engineer has over 20 years' work experience in the field of data analysis, BI, databases, development, and programming generally with Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011 to the present) in recognition of his love for Microsoft BI. Reza has been a prolific writer and co-founder at RADACAD. Reza is also the co-organizer and co-founder of the Difinity event 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 some books on MS SQL BI and also is writing other books. He was also a regular participant in online forums for technical issues such as MSDN as well as Experts-Exchange and was moderator for MSDN SQL Server forum He is also an MCP, MCSE and as an MCITP in BI. He also serves as the leader of the New Zealand Business Intelligence users group. This group is also the writer of the book that is extremely well-loved Power BI from Rookie to Rock Star, which is entirely free and comprises over 1700 pages and a second book titled Power BI Pro Architecture published by Apress.
The company is an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL group for users. And He is a Microsoft Certified Trainer.
Reza's aim is to assist users find the right solutions for data, and He's a Data enthusiast.This blog entry was filed into Power BI, Power BI from Rookie to Rockstar, Power Query and is listed under Power BI, Power BI from Rookie to Rock Star, Power Query. The following article is a good source to bookmark.

Post navigation

Share different Visual Pages using different Security Groups Power-BIAge Years Calculation that works for Leap Year in Power BI with Power Query