Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. However, as DAX is the main language usedin several calculationin the Power BI platform, a lot of people don't have any idea about this feature of Power Query. In this blog post I will describe how simple to calculateAge in Power BI with Power BI. This methodis extremely beneficial in cases where the calculation of an agecan be done using a previously calculated row-by-row basis.

Calculate Age from a date

The table is called the DimCustomer table that is part of the AdventureWorksDW table which as an age column. I've removed a few columns that weren't needed to make it easier to read;

For you to calculate the exact age each buyer, you will need:

• In Power BI Desktop, Click on Transform Data
• In the PowerQuery Editor window start by selecting the Birthdate column.
• Click on the Add Column Tab. Under the "From Date & Time" section, and then under Date choose the date range.

This is all there is to it. This is how you calculate an amount that is the sum of both the Birthdate column, along with the current date and the time.

But, the age that is displayed in the Age column, doesn't really appear like an age. It is due to an actual duration.

Duration

Duration is a particular type of data that is used to calculate the duration of a query in Power Query which represents the distinction between the two DateTime values. Duration is the mixture of four numbers:

days.hours.minutes.seconds

This is how you look at the above values. However, for the perspective of an individual, you do not want them to seek details like this. There are ways to find each part that represents the amount of time. When you go to the Duration menu , you'll be able to see that you are able to extract the amount of seconds , minutes or hours, days, and years from it.

Help when calculating the age in years like, for example, it is easy to select Total Years.

Be aware that the duration of the program is calculated in days and afterwards divided into 365 in order to provide you with the annual figure.

Rounding

No one claims they are 53.813698630136983! They call it 53, and then then round it down. It's easy to select Rounding , and Round Down from the Transform tab.

This will give you the number in years:

Then, you can remove other columns if you'd like (or perhaps you've taken advantage of transformations in the Transform tab to prevent the development of new columns) This column may be renamed as the Age column (Age:

Things to Know

• Refresh The age calculated in this way is updated every time you refresh your database. And each time it will check the birthdate with the date and date when the update was completed. This method is a pre-calculation of age. If you want the calculation to be dynamically done with DAX, I've gave you a procedure you can apply.
• The motivation behind Power Query: Benefits from performing calculations for age in Power Query is that the calculation is done while you refresh your report. You use a tool that makes calculations easier and there's no additional expense of using DAX to determine the time of runtime.
• Another option is to use these numbers intended to be used to calculate age. They only begin with the birth date. This could be used for the time of inventory on products and also the different from two days or months from each 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 degree in Computer engineering. He has more than 20 years' experience in the fields of data analysis and BI, database development, and programming mostly on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011 to the present) for his commitment toward Microsoft BI. Reza is an avid author and co-founder of RADACAD. Reza is co-founder and organizer of Difinity Conference which is held 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 ebooks on MS SQL BI and also is writing some others, Also, he has been a regular member of online technical forums such as MicrosoftDN as well as Experts-Exchange and was the moderator for MSDN SQL Server forums, and is an MCP or MCSE. He also holds the MCITP for BI. He is the creator of the New Zealand Business Intelligence users group. The group is the author of the famous publication Power BI from Rookie to Rock Star, which is available for free and has more that 170 pages of information as well as being component of Power BI Pro Architecture published by Apress.
It is an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's main goal is helping people find the right data solution. He's a Data enthusiast.This article was published within Power BI, Power BI from Rookie to Rockstar, Power Query and is covered with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

Create different visual pages and share them with different security groups in Power BIAge in Years Calculation which is able as a way to calculate Leap Year in Power Business Intelligence by using Power Query