Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, since DAX is the preferred language usedin many calculationsin Power BI, many are unaware of this feature provided by Power Query. In this article, I'll explain how easy to calculateAge in Power BI by using PowerBI. It is a methodis extremely efficient in cases where the estimate of your agecan be performed using a pre-calculated row basis.

Calculate Age from a date

Below, you will see the DimCustomer table which is part of the AdventureWorksDW table. The table has the birthdate column. I've removed the columns that aren't required so that it is easier to understand;

To calculate the actual average age of every buyer, the following information is required:

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

That's it. It will calculate the amount which is the total of the Birthdate column, along with the date and time.

But, the age appears in the Age column, however, doesn't appear to be an age. This is due to the fact that it's an actual Duration.

Duration

Duration is a distinct type of data format used in Power Query which represents the differentiating factors among the two DateTime values. Duration is a mixture of four values:

days.hours.minutes.seconds

That's the way you take the above numbers. From the perspective of the user, it's not the norm for them to comprehend the details like that. There are methods by which you are able to get each portion of the duration. With the Duration menu it will be apparent that you can take the number of seconds, minutes, hours, years and days from it.

For help calculating the age in years by way of example it is simple to click on Total Years:

The duration is calculated in days . Then, it was divided by 365 to calculate the value for the year.

Rounding

It's the truth, no one declares you old in 53.813698630136983! They call it 53, which is an inverse of 53. You can select Rounding and Round Down from the Transform tab.

This will let you know what your age is in terms of years

You can then clean the other columns, if desired (or this could mean that you made use of transformations on the Transform tab to stop creating new columns.) This column can be named column Age:

Things to Know

• Refresh The age of the data calculated in this manner will be refreshed each time you refresh your database. Every time it is refreshed, the system will be capable of comparing the birth date to the date and the timing of the refreshing. It is a method can be described as an algorithm for pre-calculating the age. If you would like your age calculation to be performed dynamically with DAX here is how I explained what you can make use of.
• The reason behind Power Query: Benefits of using age calculation using Power Query is that the calculation is made when you refresh your report. This is achieved by using an application that makes the calculation easy and quicker, and there's no additional cost for calculating it using DAX in order to determine runtime.
• Other scenarios intended for the calculation of the age of a person based on their birth date. It is possible to calculate the age of inventory in the case of products and also the various dates and dates that differ from one other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc with a concentration in Computer engineering. There are more than 20 years work experience in the field of data analysis database, BI, programming, and developing mostly using Microsoft technologies. He has been an Microsoft Data Platform MVP for nine years in a row (from 2011, to now) because of his love of Microsoft BI. Reza is known as a prolific writer and co-founder of RADACAD. Reza is also co-founder and organizer of the Difinity event 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 several books on MS SQL BI and also is working on various other books. He was also an active participant in online forums for technical issues like MSDN and Experts-Exchange and was a moderator of MSDN SQL Server forums He is also an MCP and an MCSE as well being an MCITP in BI. He is also the leader of the New Zealand Business Intelligence users group. They are also the authors of the book that is very well-loved Power BI from Rookie to Rock Star, which is entirely free and comprises an additional 1700+ pages of content along with a separate book called Power BI Pro Architecture published by Apress.
It is an International Speaker in 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 best solutions for data, and Reza is an avid Data enthusiast.This post was filed with Power BI, Power BI from Rookie to Rockstar, Power Query and is filed under Power BI, Power BI from Rookie to Rock Star, Power Query. The following article is a good source to bookmark.

Post navigation

Share Multiple Visual Pages by using different Security Groups. Power BIAge is a Years Calculation that works with Leap Year in Power BI by using Power Query